cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blaferriere Frequent Visitor
Frequent Visitor

Find beginning date/time and end date/time, calculate difference from another column

Hello,

 

So I am not sure if the title worded it correctly but I have a dataset that takes snapshots of dates in time and also captures other metrics for fuel burn. The problem that I run into is that it is accumulating the fuel burn amount throughout the history of the item.

 

My goal would be to capture how much fuel was burned for that particular day by taking the beginning date/time and the end date/time and then grab the difference in idle time and idle fuel.

 

For example, for 12/7 the daily idle time was 20356.88(from module time: 12/7/2018 10:49:53 PM) - 20355(12/7/2018 12:02:54 AM) = 1.88 for Daily Idle Fuel

 

If anyone could point me in the right direction with a link to a solution or provide me with the correct solution to put in to create this column that would be perfect, thank you all for your help!

 

Sample Dataset.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Find beginning date/time and end date/time, calculate difference from another column

@blaferriere

 

We need to make two changes.

1. The 'Module Time' column is currently text in your table. It needs to be of the type Date/Time. You can change the type in the query editor when importing the data (I see you're getting the data from an excel file)

2.  This is the reviewed code for the column, where I've just added the ALL( ) in red:

 

FuelDifference =
VAR _CurrentDateValues =
    CALCULATETABLE (
        VALUES ( Table1[Module Time] ),
        FILTER (
            ALL(Table1),
            Table1[Module Time].[Date] = EARLIER ( Table1[Module Time].[Date] )
        )
    )
VAR _EarliestDateTime =
    FIRSTNONBLANK ( _CurrentDateValues, 1 )
VAR _LatestDateTime =
    LASTNONBLANK ( _CurrentDateValues, 1 )
VAR _EarliestFuel =
    LOOKUPVALUE ( Table1[Idle fuel], Table1[Module Time], _EarliestDateTime )
VAR _LatestFuel =
    LOOKUPVALUE ( Table1[Idle fuel], Table1[Module Time], _LatestDateTime )
RETURN
    _LatestFuel - _EarliestFuel

 

Code formatted with   www.daxformatter.com

13 REPLIES 13
Super User
Super User

Re: Find beginning date/time and end date/time, calculate difference from another column

Hi @blaferriere

 

Try this for the fuel calculate column. I'm not sure whether you need the same with the Idle Time as well? If you do, you can just make a minor change to the code below with Table1[Idle time] instead of Table1[Idle fuel] 

 

 

FuelDifference =
VAR _CurrentDateValues =
    CALCULATETABLE (
        VALUES ( Table1[Module Time] ),
        FILTER (
            Table1,
            Table1[Module Time].[Date] = EARLIER ( Table1[Module Time].[Date] )
        )
    )
VAR _EarliestDateTime =
    FIRSTNONBLANK ( _CurrentDateValues, 1 )
VAR _LatestDateTime =
    LASTNONBLANK ( _CurrentDateValues, 1 )
VAR _EarliestFuel =
    LOOKUPVALUE ( Table1[Idle fuel], Table1[Module Time], _EarliestDateTime )
VAR _LatestFuel =
    LOOKUPVALUE ( Table1[Idle fuel], Table1[Module Time], _LatestDateTime )
RETURN
    _LatestFuel - _EarliestFuel

 

 

 

blaferriere Frequent Visitor
Frequent Visitor

Re: Find beginning date/time and end date/time, calculate difference from another column

Hi @AlB

 

It appears I am getting an error of circular dependency when I input the formula.

 

Thank you

Super User
Super User

Re: Find beginning date/time and end date/time, calculate difference from another column

@blaferriere

Do you have another calculated column in that table that has a CALCULATE or that uses measures?

Can you share the pbix?

blaferriere Frequent Visitor
Frequent Visitor

Re: Find beginning date/time and end date/time, calculate difference from another column

@AlB

 

There are no calculations in the file.

 

Here is the link.

PBIX File

Super User
Super User

Re: Find beginning date/time and end date/time, calculate difference from another column

@blaferriere

 

We need to make two changes.

1. The 'Module Time' column is currently text in your table. It needs to be of the type Date/Time. You can change the type in the query editor when importing the data (I see you're getting the data from an excel file)

2.  This is the reviewed code for the column, where I've just added the ALL( ) in red:

 

FuelDifference =
VAR _CurrentDateValues =
    CALCULATETABLE (
        VALUES ( Table1[Module Time] ),
        FILTER (
            ALL(Table1),
            Table1[Module Time].[Date] = EARLIER ( Table1[Module Time].[Date] )
        )
    )
VAR _EarliestDateTime =
    FIRSTNONBLANK ( _CurrentDateValues, 1 )
VAR _LatestDateTime =
    LASTNONBLANK ( _CurrentDateValues, 1 )
VAR _EarliestFuel =
    LOOKUPVALUE ( Table1[Idle fuel], Table1[Module Time], _EarliestDateTime )
VAR _LatestFuel =
    LOOKUPVALUE ( Table1[Idle fuel], Table1[Module Time], _LatestDateTime )
RETURN
    _LatestFuel - _EarliestFuel

 

Code formatted with   www.daxformatter.com

blaferriere Frequent Visitor
Frequent Visitor

Re: Find beginning date/time and end date/time, calculate difference from another column

@AlB

 

No Dice, throwing a syntax error.

Highlighted
Super User
Super User

Re: Find beginning date/time and end date/time, calculate difference from another column

@blaferriere

just edited my previous post. Forgot to mention the required type change

blaferriere Frequent Visitor
Frequent Visitor

Re: Find beginning date/time and end date/time, calculate difference from another column

@AlB

 

I am still getting a syntax error. I made the changes to the Module Time to be Date/Time and applied the query. I also added the All to the details, with no luck.

Super User
Super User

Re: Find beginning date/time and end date/time, calculate difference from another column

@blaferriere

What syntax error? It's working on my side