Forum Discussion
Difference in Values
Hello, How can I get a differene from the last value? In this example I need the difference in mileage from the last entry. (24,695 - 18,923) (30,781 - 24,695) etc Example) Shou...
tamerj1 Hi and thanks for your help.
im getting the following error:
Measure =
SUMX (
SUMMARIZE (
'PM On Time Performance Bus PM-BI-002 (2)',
'PM On Time Performance Bus PM-BI-002 (2)'[Equipment Type.Equipment Type Level 01.Key],
'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01],
'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]
),
VAR CurrentMileage = 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]
VAR CurrentDate = 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01]
VAR CurrentEquipmentTable =
CALCULATETABLE ( 'PM On Time Performance Bus PM-BI-002 (2)', ALLEXCEPT ( 'PM On Time Performance Bus PM-BI-002 (2)', 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment Type.Equipment Type Level 01.Key]))
VAR TableBefore = FILTER (CurrentEquipmentTable, 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] < CurrentDate)
VAR PreviousRecord =
TOPN ( 1, TableBefore, 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] )
VAR PreviousMileage =
MAXX ( PreviousRecord, 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage] )
RETURN
IF ( NOT ISEMPTY ( PreviousRecord ), CurrentMileage - PreviousRecord )
)
Oh! So sorry! I've wrongly copied the very last variable! Please use
Measure =
SUMX (
SUMMARIZE (
'PM On Time Performance Bus PM-BI-002 (2)',
'PM On Time Performance Bus PM-BI-002 (2)'[Equipment Type.Equipment Type Level 01.Key],
'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01],
'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]
),
VAR CurrentMileage = 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]
VAR CurrentDate = 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01]
VAR CurrentEquipmentTable =
CALCULATETABLE ( 'PM On Time Performance Bus PM-BI-002 (2)', ALLEXCEPT ( 'PM On Time Performance Bus PM-BI-002 (2)', 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment Type.Equipment Type Level 01.Key]))
VAR TableBefore = FILTER (CurrentEquipmentTable, 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] < CurrentDate)
VAR PreviousRecord =
TOPN ( 1, TableBefore, 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] )
VAR PreviousMileage =
MAXX ( PreviousRecord, 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage] )
RETURN
IF ( NOT ISEMPTY ( PreviousRecord ), CurrentMileage -PreviousMileage )
)
tamerj1 thank you!!! its still a bit off though.
Where the first one should be 0 because that is the frist entry, the second one should be 5,772 (24,695- 18,923).
third should be 6,086 (30,781-24,695) etc
Fix the date data type. It should be DateTime data type not text data type.
Still not fixed. It is clear that the date values are still aligned left indicating that this is a text. Use the query editor (power query) to define the correct data of the column. However the first value will most probably remain wrong due to a gap in the code, other than that, other values shall be correct.
however we may also add additional dax to force correct order of the columnMeasure =SUMX (SUMMARIZE ('PM On Time Performance Bus PM-BI-002 (2)','PM On Time Performance Bus PM-BI-002 (2)'[Equipment Type.Equipment Type Level 01.Key],'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01],'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]),VAR CurrentMileage = 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]VAR CurrentDate = DATEVALUE ('PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01])VAR CurrentEquipmentTable =CALCULATETABLE ( 'PM On Time Performance Bus PM-BI-002 (2)', ALLEXCEPT ( 'PM On Time Performance Bus PM-BI-002 (2)', 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment Type.Equipment Type Level 01.Key]))VAR TableBefore = FILTER (CurrentEquipmentTable, DATEVALUE ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] ) < CurrentDate)VAR PreviousRecord =TOPN ( 1, TableBefore, DATEVALUE ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] ))VAR PreviousMileage =MAXX ( PreviousRecord, 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage] )RETURNIF ( NOT ISEMPTY ( PreviousRecord ), CurrentMileage -PreviousMileage ))