Dax for last sales date by ProductID in new custom column

Sqlelyset 0
Viewed 40

I have a table with this information

ProductID Date sold
1 01/01/2022
1 02/01/2022
2 01/01/2022
2 03/01/2022
2 04/01/2022

I now want to add a new column to the table in Power BI, and set a binary flag if that's the latest sales date for the ProductID. So, my expected output is this:

ProductID Date sold Latest sales date for product
1 01/01/2022 0
1 02/01/2022 1
2 01/01/2022 0
2 03/01/2022 0
2 04/01/2022 1

How do I write the DAX for column latest sales date for product to get this output?

dax powerbi-desktop powerbi
2 Answers

With this measure

Latest sales date for product = MAX('Table'[Date sold])

you'll get

enter image description here

userhiPt0

this is the measure :

Latest sales date for product =
VAR _slct =
    SELECTEDVALUE ( 'Table'[Date sold] )
RETURN
    IF (
        CALCULATE (
            LASTDATE ( 'Table'[Date sold] ),
            ALLEXCEPT ( 'Table', 'Table'[ProductID ] )
        ) = _slct,
        1,
        0
    )

or if you want to add as a calculated column

    Latest sales date for product column = 
    
       

 IF (
        CALCULATE (
            LASTDATE ( 'Table'[Date sold] ),
            ALLEXCEPT ( 'Table', 'Table'[ProductID ] )
        ) = 'Table'[Date sold],
        1,
        0
    )
Umut K0
Related