cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
icbd
Helper I
Helper I

Subtract the min and max value for each date

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

DateHours
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.

6 REPLIES 6
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_0-1642472960331.png

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.

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!

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

 

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
Super User
Super User

Hi,

Here is one way to calculate MinMax values:

ValtteriN_0-1642029648194.png

 

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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors