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

Thank you for your reply.
Currently, and maybe for a long time, I don't have acces to the DB. So i'm just working with some csv files exported from this DB. Even if the future goal is to link the report directly to the DB, for now, I will add a file (name_week1, name_2 etc.) each week to a folder so the report will be updated. 

Since I can't work on the format of the raw values, I think I have to deal with what I have here.

My colleague tried to help me by doing everything on PowerQuery. His way is not the simplier but it works (I will explain it on my next message if I succeed). But, because of course there is a but, in his case, the "End date" column already exist, not in mine. When I did it the first time, I used : 

Date de fin = LOOKUPVALUE(
'TF GRA 700'[Date/Heure],
'TF GRA 700'[Index],
'TF GRA 700'[Index]+1)

So the end date of the event n is start date n+1 
But when I try something similar through PowerQuery, it seems to be very consumming...
Do you have any idea about how to do this PowerQuery and keep something "light" ? 

Thank you