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 🙂

     

TheoC's avatar
TheoC
Community Champion
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 🙂