Forum Discussion
Previous Date
I'm working with a table that identifies each activity as a unique row. I want to calculate the last activity date (prior to the one that row represents) for each prospect (identified by the WhoId). So, in the example below, the Prior Activity Column would say 12/6/19 where the CreatedDate column shows 12/19/19. I've read most of the forum stuff on previous dates, but none of it focuses on calculating it on the actual activity table.
I've taken some stabs at doing it through CALCULATE(MAX(), ...) and LOOKUPVALUE(), but haven't been able to get the DAX to work - usually just a standard error or a circular dependency. Any help would be greatly appreciated.
Hi,
Try this calculated column formula
=CALCULATE(MAX(Data[CreatedDate]),Filter(Data[WhoID]=Earlier(Data[WhoID])))
Hope this helps.
4 Replies
As you've discovered DAX does not natively have a concept like "the previous row". The trick people usually use is to first sort the data, then add an index column.
Then you can write a measure that gets the index in the current filter context, and then adds or subtracts 1 to get the index for the next or previous row. Then you look up a record using that index as the filter and inside that expression you can read the value from the next/previous row.
I'm a personal Power Bi Trainer I learn something every time I answer a question
The Golden Rules for Power BI
- Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
- Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
- Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
Hi,
Try this calculated column formula
=CALCULATE(MAX(Data[CreatedDate]),Filter(Data[WhoID]=Earlier(Data[WhoID])))
Hope this helps.
The solution can use earlier or you can use rank and then use earlier
https://community.powerbi.com/t5/Desktop/Explanation-of-the-EARLIER-formula/td-p/529469
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601https://community.powerbi.com/t5/Desktop/Date-Value-from-previous-record/m-p/873452
Have a look at the above entry, It might be able to be changed a little to suit your needs.
