Forum Discussion

amandabus21's avatar
amandabus21
Helper V
3 years ago

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)

Should be based on Equimpent Key and Date.

 

Thank you!

17 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      amandabus21
      Helper V
      3 years ago
      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's avatar
        tamerj1
        Community Champion
        3 years ago

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