cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

DateCircuit Comment
1/2/2019A 
1/3/2019A 
1/4/2019A 
1/5/2019A 
1/6/2019A 
1/7/2019A 
1/2/2019Bnot ok
1/3/2019Bnot ok
1/4/2019Bnot ok
1/5/2019Bworking
1/6/2019Bworking
1/7/2019Bworking
1/8/2019Bworking
1/9/2019Bworking
1/2/2019Cstatic data
1/3/2019Cstatic data
1/4/2019Cstatic data
1/5/2019Cstatic data
1/6/2019Cstatic data
1/7/2019Cstatic data
1/8/2019Cworking as desired
1/9/2019Cworking as desired
1/10/2019Cworking as desired
1/11/2019Cworking as desired

 

Desired data

 

DateCircuit CommentAge
1/7/2019A 0
1/9/2019Bworking5
1/11/2019Cworking as desired4

 

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

1 REPLY 1
Highlighted
Super User IX
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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

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.