topic Re: Difference in Values in DAX Commands and Tips

Difference in Values

amandabus21 — Tue, 21 Feb 2023 18:29:35 GMT

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!

Re: Difference in Values

tamerj1 — Tue, 21 Feb 2023 19:15:18 GMT

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

Re: Difference in Values

amandabus21 — Tue, 21 Feb 2023 19:57:34 GMT

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 )

)

Re: Difference in Values

tamerj1 — Wed, 22 Feb 2023 03:37:56 GMT

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 =

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 )

)

Re: Difference in Values

amandabus21 — Wed, 22 Feb 2023 17:48:03 GMT

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

Re: Difference in Values

tamerj1 — Wed, 22 Feb 2023 18:13:55 GMT

amandabus21

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

Re: Difference in Values

amandabus21 — Wed, 22 Feb 2023 18:55:32 GMT

tamerj1 still getting the same result

Re: Difference in Values

tamerj1 — Wed, 22 Feb 2023 19:22:39 GMT

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 =

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 )

)

Re: Difference in Values

amandabus21 — Wed, 22 Feb 2023 21:18:55 GMT

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

Re: Difference in Values

tamerj1 — Thu, 23 Feb 2023 02:31:20 GMT

amandabus21

Re: Difference in Values

amandabus21 — Thu, 23 Feb 2023 16:22:37 GMT

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

Re: Difference in Values

tamerj1 — Thu, 23 Feb 2023 16:47:34 GMT

amandabus21

Can you please share a sample file?

Re: Difference in Values

amandabus21 — Thu, 23 Feb 2023 17:13:13 GMT

It wont let me attach a PBIX file

Re: Difference in Values

tamerj1 — Thu, 23 Feb 2023 17:23:16 GMT

amandabus21

You can share a download link

Re: Difference in Values

amandabus21 — Thu, 23 Feb 2023 17:27:26 GMT

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

Re: Difference in Values

tamerj1 — Thu, 23 Feb 2023 17:38:45 GMT

amandabus21

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

Re: Difference in Values

amandabus21 — Thu, 23 Feb 2023 17:40:20 GMT

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

Re: Difference in Values

amandabus21 — Thu, 23 Feb 2023 20:01:49 GMT

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