Forum Discussion

GregB49's avatar
GregB49
Icon for Helper I rankHelper I
5 years ago
Solved

Time representation with DATEDIFF

Hi all !   I'm stuck on this one for good. Here is my problem on PowerBI. I have a sensor, that sends to the DB its state "Marche"(On) or "Arrêt"(Off). When the state change, it sends a new row, wi...
Need Help
  • AilleryO's avatar
    AilleryO
    5 years ago

    With this you get all dates required ?

     

    Tables des Dates Intervalles = 
VAR DateDebut=MIN('TF GRA 700 (2)'[Date de début])
VAR DateFin=MAX('TF GRA 700 (2)'[Date de début])
VAR TabDates=SELECTCOLUMNS(CALCULATETABLE(Dates,
                DATESBETWEEN(Dates[Date],DateDebut,DateFin)),
                "Dates TF",Dates[Date])
RETURN TabDates

     

    Then you need to add column(s), but depends on what you want.
    If you want the total hours per day or make difference by machine...

     

    Next step could be something inspired by that :

    Tables des Dates Intervalles = 
VAR DateDebut=MIN('TF GRA 700 (2)'[Date de début])
VAR DateFin=MAX('TF GRA 700 (2)'[Date de début])
VAR TabDates=SELECTCOLUMNS(CALCULATETABLE(Dates,
                DATESBETWEEN(Dates[Date],DateDebut,DateFin)),
                "Dates TF",Dates[Date])
VAR TabDates2=CROSSJOIN(TabDates,'TF GRA 700 (2)')
VAR TabDates3=ADDCOLUMNS(TabDates2,"Valeur",IF([Dates TF]>='TF GRA 700 (2)'[Date de début]&&[Dates TF]<='TF GRA 700 (2)'[Date de fin],1,0))
RETURN FILTER(TabDates3,[Valeur]=1)

    This could be done with less lines but at least it is step by step.

    You should have a table with that with all dates and a column to select only dates you are interested in.

     

    Hope this helps 

GregB49's avatar
GregB49
Icon for Helper I rankHelper I
5 years ago

Bonjour AilleryO !
First, thank you for your reply.

I'm trying to use your idea but I'm stuck again.

As you can see, I've created 4 columns Heure de début (Start Time), Heure de fin ( End time), Date de début (Start Date) and Date de fin (End date).
So at first everything seems to be ok, but in the DATESBETWEEN, I can't use my Date de début et Date de fin, I'm sure I'm doing something wrong but I don't know what. 

Do you have an idea ? 
Thanks a lot !

AilleryO's avatar
AilleryO
Icon for Memorable Member rankMemorable Member
5 years ago

Hi,

It seems you forgot to close the parenthesis of your COUNT function before the DATESBETWEEN 😉

 

  • GregB49's avatar
    GregB49
    Icon for Helper I rankHelper I
    5 years ago

    My bad 😅
    So now, the formula is working, I've just changed the return for  :

    RETURN IF (StartDate = EndDate, 'TF GRA 700 (2)'[Heure de fin] - 'TF GRA 700 (2)'[Heure de début], firstDayDuration+NbFullDays*NbHoursPerDay+LastDayDuration)    Maybe I'm wrong ?
    Otherwise, it was not returning the right answer. Now I got this :

    Also I had to change TIME(24,0,0) to TIME(23,59,59) otherwise it was returning 00:00-Start date and not 24:00 - Start date. Maybe there is a better way ? 

    On the 05/02/2021, at 14:42:13, it return 2h35m, what apparently is not good because it should be : FirstDayDuration +NbFullDay*NbHoursPerDay+LastDayDuration =
    (24-14h42) + 1*24h + 17h17 = 50h34m (approximatively) 

    So I guess it returns 2h35 because it's 50h35-24h-24h. That means, it goes back to 00h every time it goes to 24h.
     
    And I have to say, I don't understand how it will "help me" to represent 24h on the 06/02/2021 ? 

    Thanks again,
    Greg

     

    • AilleryO's avatar
      AilleryO
      Icon for Memorable Member rankMemorable Member
      5 years ago

      Hi,

      You're right, PBI doesn't propose a time format for duration that goes over 24h. But it's quite easy to format it if needed. In your case I don't think so, but otherwise you could use :

      //This measure is formatting a time in H M S
VAR HoursCalc=TRUNC(SUM(TabXXX[TotalTime]),0)//Take the whole number part for hours
VAR MinutesCalc=ROUND((SUM(TabXXX[TotalTime])-HoursCalc)*60,0)//Remain is minutes
VAR HourseCalc2=IF(LEN(HourCalc)=1,"0"&HeureCalc,HeureCalc)//Add prefix 0 to H
VAR MinutesCalc2=IF(LEN(MinutesCalc)=1,"0"&MinutesCalc,MinutesCalc)//Add prefix 0 to mn
RETURN
IF(ISBLANK(HoursCalc2),BLANK(),HoursCalc2 & " h ") & IF(ISBLANK(MinutesCalc2),BLANK(),MinutesCalc2 & " mn")

      In your case I think you need to create a table dynamically to store your values, with something like this :

      CALCULATETABLE(DateTable,
    DATESBETWEEN(DateTable[Date],StartDate,EndDate)

      This should create a Date Table with the values you need, and to add to it your column with your calculated time, have a look at :

      https://community.powerbi.com/t5/Desktop/CALCULATETABLE-ADDCOLUMNS-SUMMARIZE-multiple-columns-can-not-be/td-p/878350

      This should do the trick.

       

      Let us know...

      • GregB49's avatar
        GregB49
        Icon for Helper I rankHelper I
        5 years ago

        I'm not sure to understand... 
        I think here are the limits of my comprehension 🤔

         

        I stripped my pbix of any sensitive data, and uploaded it : 
        https://we.tl/t-9EYU25IIeB

        Could you develop a bit more your idea of the dynamic table and the link your make with the hours per day ? 

        Thank you !