calculate age of value last updated

[Vaishali04]

Hi,

I have below data. I want to calculate the age of latest comment of a circuit. for eg. for circuit "B" latest comment is "woking". That comment is not update from past 5 days. So it's age is 5.

Date	Circuit	Comment
1/2/2019	A
1/3/2019	A
1/4/2019	A
1/5/2019	A
1/6/2019	A
1/7/2019	A
1/2/2019	B	not ok
1/3/2019	B	not ok
1/4/2019	B	not ok
1/5/2019	B	working
1/6/2019	B	working
1/7/2019	B	working
1/8/2019	B	working
1/9/2019	B	working
1/2/2019	C	static data
1/3/2019	C	static data
1/4/2019	C	static data
1/5/2019	C	static data
1/6/2019	C	static data
1/7/2019	C	static data
1/8/2019	C	working as desired
1/9/2019	C	working as desired
1/10/2019	C	working as desired
1/11/2019	C	working as desired

Desired data

Date	Circuit	Comment	Age
1/7/2019	A		0
1/9/2019	B	working	5
1/11/2019	C	working as desired	4

Can anyone please help with DAX logic for calculating Age column?

[Greg_Deckler]

Well, you can get the last status using something like Lookup Min/Max: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

You can get the max date using just MAX. 

A simple age could be calculated by combing these two, so let's say you have a variable __LastComment and you have the max date, __MaxDate, you could do something like:

COUNTROWS(FILTER('Table',[Date] <= __MaxDate && [Circuit Comment] = __LastComment))

But, the issue I foresee is if you have older comments where the comment matches but there are different comments in between. For that circumstance I would have to put some thought into it but maybe something along the lines of Cthulhu. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211

In fact, if you had Cthulhu, you could just take the value of Cthulhu at your max date and be done.