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 )