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

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

     

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

        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