Forum Discussion
Loop through rows under condition and count difference
I have timestamps under each other in one column, have detected the rows where full time period starts and ends thanks to indexing. Now, how do I lopp through the column and sum the whole time in bet...
Hi Anonymous
Based on my simplified sample data, the measure could get the datediff between the start and end time.
Measure = var last_start=CALCULATE(MAX('Time'[time]),FILTER(ALL('Time'),'Time'[time]<=MAX('Time'[time])&&'Time'[end/start]="start")) return IF(MAX('Time'[end/start])="end",DATEDIFF(last_start,MAX('Time'[time]),HOUR))If you need column,you may try below dax.
Column = var last_start=CALCULATE(MAX('Time'[time]),FILTER('Time','Time'[time]<=EARLIER('Time'[time])&&'Time'[end/start]="start")) return IF('Time'[end/start]="end",DATEDIFF(last_start,'Time'[time],HOUR))Regards,
The measure you have posted did not work for me, but I've used a different approach - adding new index column to the table and then merging the table on itself.
Hi Anonymous
Based on my simplified sample data, the measure could get the datediff between the start and end time.
Measure = var last_start=CALCULATE(MAX('Time'[time]),FILTER(ALL('Time'),'Time'[time]<=MAX('Time'[time])&&'Time'[end/start]="start")) return IF(MAX('Time'[end/start])="end",DATEDIFF(last_start,MAX('Time'[time]),HOUR))
If you need column,you may try below dax.
Column = var last_start=CALCULATE(MAX('Time'[time]),FILTER('Time','Time'[time]<=EARLIER('Time'[time])&&'Time'[end/start]="start")) return IF('Time'[end/start]="end",DATEDIFF(last_start,'Time'[time],HOUR))
Regards,
The measure you have posted did not work for me, but I've used a different approach - adding new index column to the table and then merging the table on itself.