Forum Discussion
olimilo
Post Prodigy
Post ProdigyGet latest value based on max date as calculated column
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 ...
talespin
Solution Sage
Solution Sagehi 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)