Forum Discussion

Dane's avatar
Dane
Helper I
4 years ago
Solved

Calculating date/time differences

I have the following data in an SQL table. There are numerous Station ID's and each station ID has numerous scans per day. I would like to be able to get the first and last scan of each day and calcu...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    Hi Dane 

     

    There are a few ways to achieve this. Ultimately, the below will get you the following output:

     

    I've broken it down into the following steps just to allow you to follow:

     

    1. Calculated column to return the Max DateTime by Station ID:

     

    _MaxDateTimebyStationID = 

    VAR _StationID = 'Table'[Station ID]
    VAR _MaxDateTime = MAXX ( FILTER ( ALL ( 'Table' ) , 'Table'[Station ID] = _StationID ) , 'Table'[DateTime] )

    RETURN

    _MaxDateTime

    2. Calculated column to return the Min DateTime by Station ID:

    _MinDateTimebyStationID = 

    VAR _StationID = 'Table'[Station ID]
    VAR _MinDateTime = MINX ( FILTER ( ALL ( 'Table' ) , 'Table'[Station ID] = _StationID ) , 'Table'[DateTime] )

    RETURN

    _MinDateTime

    3. Calculated column to return the Variance Duration in HH:MM:SS:

     

    _TimeVariance(HHMMSS) = 'Table'[_MaxDateTimebyStationID] - 'Table'[_MinDateTimebyStationID]

    In the tabular view, you will see the output as:

    When you add a table in the canvas view, you can simply add the Station ID and drag the _TimeVariance(HHMMSS) column and get the following:

     

    I've also attached the PBIX file to assist further 🙂

     

    Hope this helps!

    Theo 🙂

     

KellerB's avatar
KellerB
Frequent Visitor
3 years ago

Hello,

Help needed!

I have pretty much the same question and the above solution is not working for me. I have multiple lines over multiple days with multiple data points throughout each day. Below is a sample but the real one has thousands of data points....

Simply speaking - I need to calculate Working hours per day

I currently have my data filtered to "this day" and just take the datediff of min and max date for each line.

However, I need to be able to change the date range so that working hours is calculated by finding the first time and last time on a line each day and doing that for each day within the date range.

Any help would be awesome!! thanks!!

LineDate

1

2022-09-20 7:30
1

 

2022-09-20 15:30
1

 

2022-09-19 8:30
1

 

2022-09-19 16:30
22022-09-20 7:30
2

 

2022-09-20 15:30
2

 

2022-09-19 8:30
2

 

2022-09-19 16:30
32022-09-20 6:30
3

 

2022-09-20 15:30
3

 

2022-09-19 6:30
3

 

2022-09-19 16:30