Forum Discussion

StephenK2022's avatar
StephenK2022
Frequent Visitor
4 years ago
Solved

Maximum Time in mins based on date range from slicer

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

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )

8 Replies

  • StephenK2022's avatar
    StephenK2022
    Frequent Visitor
    4 years ago

    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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.