Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 between all the start and end rows? See sample snapshot:

 

 sample values. Desired value of first difference is 30 hours

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

8 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Because the column looks like this, with more specified periods for one id. v-cherch-msft More sample rows