Forum Discussion

jmhoskinson's avatar
jmhoskinson
Icon for Helper I rankHelper I
6 years ago
Solved

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). ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Try this calculated column formula

    =CALCULATE(MAX(Data[CreatedDate]),Filter(Data[WhoID]=Earlier(Data[WhoID])))

    Hope this helps.

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

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

  1. 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
  2. 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
  3. 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.