Forum Discussion
Solved
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 t...
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
AlB
Community Champion
Community ChampionHi 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
- AlB
Do you have another calculated column in that table that has a CALCULATE or that uses measures?
Can you share the pbix?
