Forum Discussion

amandabus21's avatar
amandabus21
Helper 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 =
        CALCULATE ( MAX ( 'PM On Time Performance Bus PM-BI-002 (2)'[Mileage]) )
    VAR prev =
        MAXX (
            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)'[Completion Date.Completion Date Level 01] < EARLIER ('PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] )
            ),
    [Mileage]
        )
    RETURN
        curr - prev
  • 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]
    )
    )

14 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    HI amandabus21 
    Please try

    Variable Difference =
VAR CurrentSort =
    'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] * 1000000
        + INT ( '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 T =
    ADDCOLUMNS (
        'PM On Time Performance Bus PM-BI-002 (2)',
        "@Sort",
            'PM On Time Performance Bus PM-BI-002 (2)'[Equipment.Equipment Level 01.Key] * 1000000
                + INT ( 'PM On Time Performance Bus PM-BI-002 (2)'[Completion Date.Completion Date Level 01] )
    )
VAR Prev =
    MAXX ( FILTER ( T, [@Sort] < CurrentSort ), [Mileage] )
RETURN
    Curr - Prev
    • amandabus21's avatar
      amandabus21
      Helper 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
        Community 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 )