MAX & Filter

[Anonymous]

Dear all,

I have a table with data from different stores and also dates of these data.

I now want to include a new column (lastest Data date) which contains an X if this row is the latest data for the relevant store or an "-" if newer data is available.

Please see example including expected values for the new column "latest data date".

Date                            Store                  Latest data Date                          

01.01.2020                     A                              -

02.01.2020                     A                              -    

03.01.2020                     A                              X

01.01.2020                     B                              -

02.01.2020                     B                              X

Can anybody help?

Thanks

BAstian

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a calculated column as below:

Latest data Date = 
VAR _latestDate =
    CALCULATE (
        MAX ( 'Store'[Date] ),
        FILTER ( 'Store', 'Store'[Store] = EARLIER ( 'Store'[Store] ) )
    )
RETURN
    IF ( 'Store'[Date] = _latestDate, "X", "-" )

Best Regards

Rena

[parry2k]

@Anonymous you can add a new column using the following expression

LatestDate = 
VAR __latest = CALCULATE ( MAX ( Latest[Date] ), ALLEXCEPT ( Latest, Latest[Store] ) )
RETURN
IF ( __latest = Latest[Date], "X" , "-" )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡