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

[Anonymous]

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 calculate on the filtered data. My data set is 60,000 rows and the measure I have to do this works, but not for my large data set. It will calculate endlessly without providing any results. I have used the measure in a small data set and had success (Ill post measure below). In excel I could subtract two cells in the same column easily but am having difficulty doing this in power bi. In my data set I have the UNIX time (column f_crtm) and a column labeled "Date" which has the following query to calculate: #datetime(1970,1,1,0,0,0)+#duration(0,-5,0,[f_crtm])

Basically, my end goal is to have a table visualization that I can filter and calculate the time differences between rows with a large data set.

Measure: 

[Anonymous · Accepted solution]

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]

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.