newest unit price for a customer

[MathiesJeppesen]

goal: find the newst unit price for a customer. 

I have a sales unit price tabel that is conected to a customer tabel. it is currently not connected to the date table.

the sales unit price have a customer number, startdate, enddate and unitprice. 

customer number	startdate	end date	unitprice
1			1000
2		31/12/2023	1700
2	1/1/2024		1600
3	1/1/2024		1200
4	1/1/2024	1/1/2025	2000

as u can see 1) the start date and end date can both be empty. 2) start date and end date can be in 2 seperate rows. 3) start date can be filled but there is no end date and 4) both start date and end date can be filled on the same row. 

I made this code but it only works when start date and end date is in two rows

Measure =
CALCULATE (
    SUMX (
         VALUES ( sales unit price[customer] ),
         SUM ( sales unit price[Unit_Price] )
    ),
FILTER (
    sales unit price,
    MAX ( sales unit price[Starting_Date] )
        >= MAX ( sales unit price[End_Date]  )
      )
)

the end reuslt should be to return a measure the newest unitprice

customer	measure to calculate unitprice
1	1000
2	1600
3	1200
4	2000

[Greg_Deckler · Accepted solution]

@MathiesJeppesen Lookup Min/Max - Microsoft Fabric Community

[v-xiandat-msft · Accepted solution]

Hi @MathiesJeppesen ,

Thank you for @Greg_Deckler  answer , and I have other suggestions for you:

Below is my table:

The following DAX might work for you:

Measure = 
   VAR _max_start = CALCULATE(MAX('Table'[startdate]),ALLEXCEPT('Table','Table'[customer number]))
   VAR _max_end = CALCULATE(MAX('Table'[end date]),ALLEXCEPT('Table','Table'[customer number]))
   VAR result = IF(_max_start >= _max_end , CALCULATE(MAX('Table'[unitprice]),FILTER('Table','Table'[startdate] = _max_start),ALLEXCEPT('Table','Table'[customer number])), CALCULATE(MAX('Table'[unitprice]),FILTER('Table','Table'[end date] = _max_end),ALLEXCEPT('Table','Table'[customer number])))
   RETURN
   result

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MathiesJeppesen]

HI

It looks to be working and i will dobbel check it tomorrow! thx a lot

    VAR __Table =

        SUMMARIZE(

            'sales unit price',

            sales unit price[Sales_Code], Sales unit price[Item_No],

            "__Value",MAX('Sales unit price'[Unit_Price])

        )

    VAR __Max = MAXX(__Table,[__Value])

RETURN

    MAXX(FILTER(__Table,[__Value] = __Max),max(sales unit price[Unit_Price]))

[Greg_Deckler · Accepted solution]

@MathiesJeppesen Lookup Min/Max - Microsoft Fabric Community