Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

I calculate in hours between two different record dates

Good morning community, I would need the following help. Currently I have different order numbers and in the factory they would need to know the difference in hours between the end of one order a...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Syndicate_Admin RWRW 
Yes becuase power bi automatically hides the blank measure value. We can use the following simple trick

Time Measure =
// Measure to calculate the time difference between the completion of one batch and the beginning of the next batch
VAR SelectedTable =
    FILTER (
        ALLSELECTED ( Pmx[Start Date], Pmx[End Date] ),
        Pmx[End Date] <> BLANK ()
    )
VAR CurrentStart =
    MAX ( Pmx[Start Date] )
VAR PreviousEnd =
    MAXX ( FILTER ( SelectedTable, Pmx[Start Date] < CurrentStart ), Pmx[End Date] )
VAR TimeDifference =
    DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
VAR Result =
    DIVIDE ( TimeDifference, 60, " " )
RETURN
    Result
Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

The same thing continues to happen to me. I need that without the mesasure of INdateRange I keep the records whose time is White, I always mean the first record of the table will be white since it will be the first order depending on the selected dates.

With the InRangeDate measure

without the InRangeDate measure

I would need the final solution to come out like this

I hope I have explained, I am waiting

Thank you very much for your help

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

    Hi Syndicate_Admin , RWRW 
    I hope the following solves the problem

    Time Measure =
// Measure to calculate the time difference between the completion of one batch and the beginning of the next batch
VAR SelectedTable =
    FILTER (
        ALLSELECTED ( Pmx[Start Date], Pmx[End Date] ),
        Pmx[End Date] <> BLANK ()
    )
VAR CurrentStart =
    MAX ( Pmx[Start Date] )
VAR PreviousDatesTable =
    FILTER ( SelectedTable, Pmx[Start Date] < CurrentStart )
VAR PreviousEnd =
    MAXX ( PreviousDatesTable, Pmx[End Date] )
VAR FirstSelectedDate =
    MINX ( PreviousDatesTable, Pmx[End Date] )
VAR TimeDifference =
    DATEDIFF ( PreviousEnd, CurrentStart, MINUTE )
VAR Result =
    DIVIDE ( TimeDifference, 60, " " )
RETURN
    IF ( PreviousEnd = FirstSelectedDate, " ", Result )