Forum Discussion
Find beginning date/time and end date/time, calculate difference from another column
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
Community Championjust edited my previous post. Forgot to mention the required type change
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.
- AlB
What syntax error? It's working on my side
- AlB
- AlB
Cool. Perhaps some kudos would be appropriate then after so much back and forth? :smileyhappy:
It's saying that the following:
The syntax for 'table' is incorrect. (DAX(VAR _CurrentDateValues = CALCULATETABLE ( VALUES ( table[Module Time] ), FILTER ( ALL(table), table[Module Time].[Date] = EARLIER ( table[Module Time].[Date] ) ) )VAR _EarliestDateTime = FIRSTNONBLANK ( _CurrentDateValues, 1 )VAR _LatestDateTime = LASTNONBLANK ( _CurrentDateValues, 1 )VAR _EarliestFuel = LOOKUPVALUE ( table[Idle fuel], table[Module Time], _EarliestDateTime )VAR _LatestFuel = LOOKUPVALUE ( table[Idle fuel], table[Module Time], _LatestDateTime )RETURN _LatestFuel - _EarliestFuel)).