Maximum Time in mins based on date range from slicer

[StephenK2022]

Hi all,

First time using this forum so I hope I'm posting in the right place.

I am trying to work out how to dynamically change the amount of minutes in a day based on the date range chosen in a slicer. Basically I am looking for the unnaccounted for minutes on machines in our factory.

Currently I have set up a column that has all values as 1440 (max minutes in a day) and subtract all the accounted for minutes to work out what is missing - When I change this to run over say a weekend, It is still working out unnaccounted for minutes based on one day. Is there a function where Power BI can work out how many total minutes are available based on the date range in the slicer?

Below are my graphs for reference.

Over a one day period

Over a weekend

Many Thanks in advance,

Steve

[StephenK2022]

Hi @tamerj1 ,

Thanks for your reply, 

I am using a measure to work out the unaccounted for mins, the problem is that i am using a column with only 1440 in it. So when I change the date I need a function which will change the value to the maximum minutes over that time period - Basically all the bars in the chart should add up to the total minutes over that time period and all should be the exact same height.

Logged mins vs total mins (Total Accounted for Mins minus Average of TOTAL MINUTES) =

IF(AVERAGE('24hr production report'[TOTAL MINUTES]) - (SUM('24hr production report'[Total Accounted for Mins]))<0,(AVERAGE('24hr production report'[TOTAL MINUTES]) - (SUM('24hr production report'[Total Accounted for Mins])))*-1,AVERAGE('24hr production report'[TOTAL MINUTES]) - (SUM('24hr production report'[Total Accounted for Mins])))

[tamerj1]

@StephenK2022 

Is the date slicer from the same table?

[StephenK2022]

@tamerj1 

Yes - It is the "Dates" column in the photo.

Thanks

[tamerj1]

@StephenK2022 

Ok Please try

Logged mins vs total mins (Total Accounted for Mins minus Average of TOTAL MINUTES) =
VAR FirstDateInFilter =
    MINX (
        ALLSELECTED ( '24hr production report'[Dates] ),
        '24hr production report'[Dates]
    )
VAR LastDateInFilter =
    MAXX (
        ALLSELECTED ( '24hr production report'[Dates] ),
        '24hr production report'[Dates]
    )
VAR TotalMinutes =
    DATEDIFF ( FirstDateInFilter, LastDateInFilter, MINUTE )
VAR AccountedMinutes =
    SUM ( '24hr production report'[Total Accounted for Mins] )
RETURN
    ABS ( TotalMinutes - AccountedMinutes )

[StephenK2022]

@tamerj1 

Thanks very much, this worked for date ranges over 2 days, however now when we look over one day we are getting this:

Over a longer time period:

I'm guessing it is an issue due to "first date in filter" and "last date in filter" being the same value?

Thanks again

[tamerj1 · Accepted solution]

@StephenK2022 
Yes becuase DATEDIFF return 0 for the same date difference. Please try

Logged mins vs total mins (Total Accounted for Mins minus Average of TOTAL MINUTES) =
VAR FirstDateInFilter =
    MINX (
        ALLSELECTED ( '24hr production report'[Dates] ),
        '24hr production report'[Dates]
    )
VAR LastDateInFilter =
    MAXX (
        ALLSELECTED ( '24hr production report'[Dates] ),
        '24hr production report'[Dates]
    )
VAR TotalMinutes =
    DATEDIFF ( FirstDateInFilter, LastDateInFilter + 1, MINUTE )
VAR AccountedMinutes =
    SUM ( '24hr production report'[Total Accounted for Mins] )
RETURN
    ABS ( TotalMinutes - AccountedMinutes )

[StephenK2022]

@tamerj1 

That seems to have done the trick - Thank you so much, this will be very helpful.

I wil run a few more tests just to make sure and then I can accept as solution for you.

Thanks again

[tamerj1]

Hi @StephenK2022 

Nice chart! 
It seems you are utilizing calculated columns which cannot be dynamic. You need to use measures instead. Please provide more details and a sample data along with the expected results.