Search code examples
powerbidaxcalculated-columns

Power BI: DAX - How to identify last occurence of specific item during month?

I would like to ask you for help. I would like to know how to identify last occurence of specific item during month via calculated column in Power BI by DAX.

Can you advice me how to write DAX formula please? Many thanks for your hint.

I expect these output in column Last Occurence (rows in my table could be mixed, this is only nice shape for my clear explanation):

Month Update Item Last Occurence
1 01.01.2023 A FALSE
1 10.01.2023 A TRUE
1 02.01.2023 B FALSE
1 03.01.2023 B TRUE
2 01.02.2023 A FALSE
2 10.02.2023 A FALSE
2 15.02.2023 A TRUE
3 08.03.2023 A TRUE
4 12.04.2023 A TRUE

Many thanks for your help.

Solution

  • Assumes table is named Table1:

    =
VAR ThisItem = Table1[Item]
VAR ThisMonth = Table1[Month]
RETURN
    Table1[Update]
        = CALCULATE(
            MAX( Table1[Update] ),
            FILTER( Table1, Table1[Item] = ThisItem && Table1[Month] = ThisMonth )
        )