Loop through rows under condition and count difference

[Anonymous]

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

[v-cherch-msft · Accepted solution]

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,

[v-cherch-msft]

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]

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]

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

[Anonymous]

The measure you've provided just does not do the job. I've separated the times into two columns and just need do substract them from each other. If there is end time provided, substract start time from it otherwise from now time substract start time. As in pictures provided.

snippetmeasure

[v-cherch-msft]

Hi @Anonymous 

If you have resolved the issue, please mark the right reply as answer, If not, please share more details for us so that we could help further on it.

Regards,

[Anonymous]

MORE DETAILS:
The problem I am solbing is to try to sum the time of full occupancy of a box. I have a table with measurements 3-6 per day per box. Single measurement is in a single row, with percent_calculted stating the level of occupancy of the box and measured_at_utc stating the time of measurement. 

id is measurements unique ID, code is container's unique code

Based on this data, using indexed columns, I got the earlier values of percent_calculated, time and code into the next row as can be seen in picture below

Thanks to that, I've been able to determine which time is start time of full occupancy of a box and which is the end time (if code = added code & percent> added percent etc).

Hence, I have the start and end times determined and now I need to count the difference between them. The issue is that there are thousands of them with unknown number of blanks between the two values and I need to loop through all of them. 

The desired result is time of full occupancy, so I can determine for example longest period of full occupancy, average for all boxes, average for box, max, min etc etc etc.

Thank you for your time @v-cherch-msft 

[v-cherch-msft · Accepted solution]

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 · Accepted solution]

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.