Difference in Values

[amandabus21]

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)

Should be based on Equimpent Key and Date.

Thank you!

[tamerj1]

Hi @amandabus21 

please try

=
SUMX (
SUMMARIZE (
'Table',
'Table'[EquipmentKey],
'Table'[CompletionDate],
'Table'[Mileage]
),
VAR CurrentMileage = 'Table'[Mileage]
VAR CurrentDate = 'Table'[CompletionDate]
VAR CurrentEquipmentTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[EquipmentKey] ) )
VAR TableBefore =
FILTER ( CurrentEquipmentTable, 'Table'[CompletionDate] < CurrentDate )
VAR PreviousRecord =
TOPN ( 1, TableBefore, 'Table'[CompletionDate] )
VAR PreviousMileage =
MAXX ( PreviousRecord, 'Table'[Mileage] )
RETURN
IF ( NOT ISEMPTY ( PreviousRecord ), CurrentMileage - PreviousRecord )
)

[amandabus21]

@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 )

)

[tamerj1]

@amandabus21 

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 )

)

[amandabus21]

@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

[tamerj1]

@amandabus21 

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 column

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 = 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] )

RETURN

IF ( NOT ISEMPTY ( PreviousRecord ), CurrentMileage -PreviousMileage )

)

[amandabus21]

@tamerj1 changed it in power query, and still getting the same thing 😞

[tamerj1]

@amandabus21 

 

[amandabus21]

@tamerj1 That isnt the date i was using, but I changed it anyways. Still getting the same wrong values. 

[tamerj1]

@amandabus21 

Can you please share a sample file?

[amandabus21]

It wont let me attach a PBIX file

[tamerj1]

@amandabus21 

You can share a download link

[amandabus21]

Ok got it: 

Change in Mileage = SUMX (

    FILTER (

        'PM On Time Performance Bus PM-BI-002 (2)',

        'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] <= EARLIER ( 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] )

    ),

    'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]

)

just need to apply the 0 else from before

[amandabus21]

I'm not sure where I would create a link from.

Maybe this may make more sense, this is the orginal Variable equation from Business Objects: 

(Equipment key is Equipmemt.Equipment Level 01.Key) 

=(If Previous([Equipment - Key]) <> [Equipment - Key] Then 0 Else [Mileage] - Previous([Mileage]))

[amandabus21]

@tamerj1 thank you so much for all your help! Just have one more question, do you know if I can put a rule or condition for that DAX statement to run only if the Equipment does not equal the previous? Otherwise it should default to 0

[tamerj1]

@amandabus21 

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

[tamerj1]

@amandabus21 

Fix the date data type. It should be DateTime data type not text data type. 

[amandabus21]

@tamerj1 still getting the same result