topic Re: Get latest value based on max date as calculated column in DAX Commands and Tips

Get latest value based on max date as calculated column

olimilo — Mon, 29 Jan 2024 13:13:22 GMT

Example data below:

ID	Fty	Status	Date
1	Facility A	Active	1/1/2020
2	Facility B	Active	1/1/2021
3	Facility A	Active	1/1/2021
4	Facility C	Active	1/1/2022
5	Facility A	Inactive	1/1/2022
6	Facility B	Active	1/1/2023

I'm trying to get the Status of the Facility based on the Date column. I am able to do this as a measure below however I would like to get it as a calculated column?

Expected output:

Fty		Status
Facility A		Inactive
Facility B		Active
Facility C		Active

~Current Status = VAR _val = MAX('Data'[Date]) RETURN MAXX( FILTER( 'Data', 'Data'[Date] = _val && 'Data'[Fty] = MAX('Data'[Fty]) ), 'Data'[Status] )

Re: Get latest value based on max date as calculated column

talespin — Mon, 29 Jan 2024 13:42:03 GMT

hi olimilo ,

Please test this.

First calculating max date for each facility by removing all filters on table except facility.

Then using this date to find the status for each facility.

CurrentStatus =

VAR _fty = TestTable7[Fty]

VAR _maxDateFty = CALCULATE(MAX(TestTable7[Date]), REMOVEFILTERS(TestTable7), TestTable7[Fty] = _fty)

return CALCULATE(MAX(TestTable7[Status]), REMOVEFILTERS(TestTable7), TestTable7[Fty] = _fty && TestTable7[Date] = _maxDateFty)

Re: Get latest value based on max date as calculated column

v-weiyan1-msft — Tue, 30 Jan 2024 02:36:14 GMT

Hi olimilo ,

talespin nice method！And you can also consider using the following code to meet your needs.
Based on your description, please try code as below to create a Calcualted table.

New Table = VAR MaxDate = MAXX ( ALLEXCEPT ( 'Table', 'Table'[Fty] ), 'Table'[Date] ) VAR TempTable = ADDCOLUMNS ( VALUES ( 'Table'[Fty] ), "Status", MAXX ( FILTER ( 'Table', 'Table'[Fty] = EARLIER ( 'Table'[Fty] ) && 'Table'[Date] = [MaxDate] ), 'Table'[Status] ) ) RETURN TempTable

Result is as below.

Best Regards,
Yulia Yan

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