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 🙂

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Dane 

 

May I ask if your problem has been solved? Did TheoC's post above help you? If any of the posts are helpful, then please consider Accepting it as a Solution to help other members find it faster. Many thanks!

 

If this doesn't work for you, would you mind providing some data that does not contain private information?

It makes it easier to give you a solution.

  1. Sample (dummy dataset) data as text, use the table tool in the editing bar
  2. Expected output from sample data
  3. Explanation in words of how to get from 1. to 2.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

KellerB's avatar
KellerB
Frequent Visitor
3 years ago

I have the same question and the solution provided seems to only look at the min and max date so it still includes downtime. I use a slicer to change the timeframe from a day to 20 days to 380 days (any amount of days) and need the working hours for each specific day and then sum those all up.