Forum Discussion

GregB49's avatar
GregB49
Helper 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
Helper 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 !

 

GregB49's avatar
GregB49
Helper I
5 years ago

Okay with Plainly information, it did something like that :

 

 

Var myTable = CALCULATETABLE(
    SUMMARIZE('TF GRA 700 (2)','TF GRA 700 (2)'[Date de début],"Heures par jour", SUM('TF GRA 700 (2)'[Jour passé])),
    DATESBETWEEN(Dates[Date],MIN('TF GRA 700 (2)'[Date de début]),MAX('TF GRA 700 (2)'[Date de fin])), 'TF GRA 700 (2)'[Etat]="Marche"
)

Return myTable

 

 

What returns me : 

 


I think the error is from my formula, because I still don't have the missing days...
But it seems we are getting closer, but I don't know in what direction to go...

  • AilleryO's avatar
    AilleryO
    Memorable Member
    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
    Helper I
    5 years ago

    You, mister, are a freaking genius 😍
    I used your technique and I "just" had to find the right switch function that respect every "time bounderies" 

    If someone needs it :

     

    TFonctionnement = SWITCH(TRUE(),
'Tables des Dates Intervalles'[Dates TF]='Tables des Dates Intervalles'[Date de début] && 'Tables des Dates Intervalles'[Dates TF] = 'Tables des Dates Intervalles'[Date de fin], 'Tables des Dates Intervalles'[Jour passé],
'Tables des Dates Intervalles'[Dates TF]<'Tables des Dates Intervalles'[Date de fin] && 'Tables des Dates Intervalles'[Dates TF]>'Tables des Dates Intervalles'[Date de début],TIME(23,59,59),
'Tables des Dates Intervalles'[Dates TF]='Tables des Dates Intervalles'[Date de début],TIME(23,59,59)-'Tables des Dates Intervalles'[Heure de début],
'Tables des Dates Intervalles'[Heure de fin])

     

    I now have a total of 23h59m59s per day, and I just have to filter on Marche or Arrêt to have the time in the state per day, like this (filtered on "Marche") : 

     

    Now, i'll work on how many state change per day etc. 

    A big thanks for your help huys, really appreciate it ! 
     

  • AilleryO's avatar
    AilleryO
    Memorable Member
    5 years ago

    Hi,

    Your SUMMARIZE function, groups by Date Début.

    If you remove it, do you have all required dates ?

    Maybe all dates are the same range for any log ?

    I'll have a look at the pbix file you sent...

  • GregB49's avatar
    GregB49
    Helper I
    5 years ago

    Yes, I thought it was the groupement to do ? If I remove it, it return only one row, which seems to be the sum of every hour.

    But I do agree, I don't see anything in the formula that would help PowerBi to understand what I want 😁