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
You may try to create a measure like below.
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 )
)
Regards,
Thank you for the help. However, the solution does not work for me. How can you make this measure iterate over the whole table with more than one start-end period possible for every id? v-cherch-msft