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 i...
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 )
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

StephenK2022 

Is the date slicer from the same table?

  • StephenK2022's avatar
    StephenK2022
    Frequent Visitor
    4 years ago

    tamerj1 

     

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

     

    Thanks

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        StephenK2022
        Frequent Visitor
        4 years ago

        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