cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## calculate age of value last updated

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

Highlighted
Super User IX

## Re: calculate age of value last updated

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.

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors