Subtract the min and max value for each date

[icbd]

Good day everyone, 

I've been hacking away at this particular problem for a little bit. I would like to do two different things.

First, I would like to calculate actual runtime of a machine according to my filters. I have a date column, and an hour (displayed as a decimal number) column. I also have a calendar table.

What I am trying to achieve, is for the selected dates in my date filter:

For each date, subtract the MIN value for the hours to the MAX value, and sum this. Example below

Date	Hours
2021-10-13	7.5
2021-10-13	23.25
2021-10-14	8
2021-10-14	23

The result, for the above example, should be 30.75 hours. Getting the result for single day is easy with the min max functions but I have yet to find a way to make this work reliably with multiple days.

The second thing I am trying to achieve is to calculate the planned time. I know the length of each shift, I have 3 shifts per day (but not always).

I made a column which has a ShiftId (1 for day, 2 for evening, 3 for night) and another column that combines this with the date such as I would have, for today 12022-01-12, 22022-01-12, 32022-01-12 to be able to do some DISTINCTCOUNT for the number of shifts. The shifts are not the same length.

The planned time calculation should work according to how ever many date are selected in my filter. 

The night shift is 6.5 hours, day shift is 7.5 hours and evening shift is 7 hours.

Say I have two days, one with all three shifts and another with just evening and day, my total should come out to:

6.5+7.5+7+7.5+7=35.5 hours.

That works perfect when I look at past data, but if I want today's data and the day isn't done:

Night shift is finished (6.5)+Day shift is finished(7.5)+Wherever I am in the shift right now = planned time as of now.

I hope this makes sense. Thanks to any and all that can provide an input, I'm quite new to this.

[v-chenwuz-msft]

Hi @icbd ,

Maybe you can try this code.

Measure =
VAR _1 =
    ADDCOLUMNS(
        'Table',
        "ifmin",
            IF(
                [Hours]
                    = CALCULATE(
                        MIN( 'Table'[Hours] ),
                        FILTER( 'Table', 'Table'[Date] = EARLIER( 'Table'[Date] ) )
                    ),
                1,
                0
            )
    )
RETURN
    SUMX( FILTER( _1, [ifmin] = 0 ), [Hours] )
        - SUMX( FILTER( _1, [ifmin] = 1 ), [Hours] )

result:

Pbix in the end you can refer.

2 please share some data without sensitive data about your second thing, ShiftId. And expect result.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[icbd]

Hi v-chenwuz-msft, this gets me close, but it seems to ignore my current date filter and is calculating the entire table, thank you for the help so far!

[v-chenwuz-msft]

Hi @icbd ,

You can try to add filter() to replace the "table" which is the first parameter of ADDCOLUMNS(). Or maybe the relationship between data table and date table limits the current date filter.

Please share your pbix file without sensitive data, if you need more help.

Best Regards

Community Support Team _ chenwu zhu

[icbd]

That did not work, but I think I now know why. I cannot upload the pbix as everything is direct queries so you'd have no data to work with.

The reason this does not work is that I have multiple hours in the same day, for example:

Date	Hours
2022-01-20	7.25
2022-01-20	8.00
2022-01-20	10.37
2022-01-20	15.00
2022-01-20	23.25
2022-01-21	8.18
2022-01-21	9.45
2022-01-21	11.77
2022-01-21	23.22

In this particular case, say I am filtering the data to see those two days, the calculation I want Power BI to do would be the last value of 2022-01-20 minus the first value of 2022-01-20, and the same for 2022-01-21.

This would give me: 23.25 - 7.25 + 23.22 - 8.18 = 31.04

The measure you gave me unfortunately sums the whole thing and gives me numbers in the millions as there are roughly 4000 entries per hour in my database.

I appreciate the help you have given so far, I'm quite new to this.

[ValtteriN]

Hi,

Here is one way to calculate MinMax values:

MinMax =

var cdate = SELECTEDVALUE(MinMax[Date])

var minV = calculate(MIN(MinMax[Hours]),MinMax[Date]=cdate,ALL(MinMax))

var maxV = calculate(MAX(MinMax[Hours]),MinMax[Date]=cdate,ALL(MinMax))

return

minV-maxV

For the shift calculation I would need some sample data to visualize the problem.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[icbd]

Hello ValtteriN,

Unfortunately this does not work for multiple days selected, which is the reason I need it.

Doing it over a single day is actually quite easy for me as I already have measures calculating the first value and the last value over the interval I chose, this breaks down once I choose multiple days, of course, because it takes the ealiert hour of the first day and the latest hour of the last day.