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) Shou...
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 )
      )
      • amandabus21's avatar
        amandabus21
        Helper V
        3 years ago

        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