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 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello,

 

Above you talk about DB, do you have the chance to make SQL query, is there calendar table available?

With the following structure you can make a new table where are all the dates when a machine was used, and the start and end dates. From that you are likely to get to your end result with some SQL or Power Query with simple operations.

 

select 
dim_calendar.date_id, 
event_selection.event_id, 
event_selection.event_begin_date,
event_selection.event_end_date
from
event_selection,
dim_calendar
where
event_selection.event_begin_date <= dim_calendar.date_id
AND event_selection.event_end_date >= dim_calendar.date_id

In the end of my blog post: https://www.plainlyresults.com/blog/power-bi-dax-how-to-summarize-data-from-multiple-tables/ I cover when DAX might not be the best tool for the job.