Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Finding the difference between two filtered Date/Time rows with large data set

Hi All,   I need to create a measure that will subtract two times in seperate rows and provide me with the difference. I need to be able to filter my data using slicers and have the measure calcula...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous,

    AFAIK, power bi data model table not contains row/column index.

    For this scenario, you need to use specific field value as index field(in your DAX expression), then you can extract current row value and lookup table records who contain 'index' value less/large than current row and setting them as previous/next row.

    For example 'DateTime' field:

    CurrentDate =
MAX ( Table[Date] )

PreviousDate =
CALCULATE (
    MAX ( Table[Date] ),
    FILTER ( ALLSELECTED ( Table ), [Date] < currentDate )
)

NextDate =
CALCULATE (
    MIN( Table[Date] ),
    FILTER ( ALLSELECTED ( Table ), [Date] > currentDate )
)

    Regards,

    Xiaoxin Sheng

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

You are correct. DAX is not like EXCEL and does not have a concept of "previous row" or "next row".

The first step to compare values in 2 rows is to make sure you data is sorted correctly and then add an index column in the query editor.
Once you have an index you can write a measure that 

1) gets the index of the current row (  VAR cur_index = selectedvalue(index_column))
2) adds or subtracts 1 to the cur_index
3) uses LookupValue() to lookup the row that has the new index and retrieves the value from it.

 

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.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.