Forum Discussion

amandabus21's avatar
amandabus21
Icon for Helper V rankHelper V
3 years ago
Solved

COnditonal Column

How can I change this formula so that it only calculates if the previous Equipment Key does not equal the next Equipment Key.  It then should be 0.   Variable Difference =     VAR curr =      ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    amandabus21 

    Please try

    MeasureDifference =
    VAR FirstSelectedDate =
    CALCULATE (
    MIN ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] ),
    ALLSELECTED (),
    VALUES ( 'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] )
    )
    RETURN
    SUMX (
    'PM On Time Performance Bus PM-BI-002 (2)',
    IF (
    'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] = FirstSelectedDate,
    0,
    'PM On Time Performance Bus PM-BI-002 (2)'[Variable Difference]
    )
    )

amandabus21's avatar
amandabus21
Icon for Helper V rankHelper V
3 years ago

tamerj1 thank you, but not quite what I was looking for. 

 

For example, for Equipment key 1104, it should just "Else=0" because there is no previous equipment key record for that equipment. In Equipment 1106 it should calculate the "current - previous" mileage because there is a previous entry. 

 

 

What it should look like:

 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

amandabus21 
Perhaps I misunderstood your requirement. I'm still not sure if I fully understand 🙂

Please try

Variable Difference =
VAR CurrentDate = 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01]
VAR Curr = 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]
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.Equipment 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 Prev =
    MAXX ( PreviousRecord, 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage] )
RETURN
    Curr - COALESCE ( Prev, Curr )