Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Check if previous value is the same or different - Comparing data across dates

Hi.

I have a data feed of hardware used within a company that is reviewed every morning. The goal of the DAX is to check if the hardware on a particular node has changed, and the equation below does this perfectly, by comparing todays value against yesterdays, and if there is a change, the output is 1, i.e, hardware swapped. If the node is a brand new node there will not be any information for yesterday so the output is 0.

Unfortunately there are days when the data feed can become corrupt and we may not receive it for a few days or sometimes a few weeks. The equation below only compares todays data with yesterdays data so if yesterdays information/data feed is missing the output is 0, even if hardware was swapped. Is there a way to change the equation to look for the most recently available data to compare todays data against?

 

Hardware Swap =
VAR __var1 = 'FieldReplaceableUnit_productData'[f_serialNumber]
VAR __var2 = CALCULATE(VALUES('FieldReplaceableUnit_productData'[f_serialNumber]), FILTER(ALL('FieldReplaceableUnit_productData'), 'FieldReplaceableUnit_productData'[Date created] = EARLIER('FieldReplaceableUnit_productData'[Date created]) - 1 && 'FieldReplaceableUnit_productData'[managedElementId/fieldReplaceableUnitId] = EARLIER('FieldReplaceableUnit_productData'[managedElementId/fieldReplaceableUnitId])))
RETURN
IF(ISBLANK(__var2), 0, IF(__var1 = __var2, 0, 1))
 
[Date created] is that date of the arrival of data feed for hardware information
Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    Please check it this is the output that you're looking for:


    The Measure I'm using is this:

    New Hardware Swap = 
VAR _SerialNumber = SELECTEDVALUE(FieldReplaceableUnit_productData[f_serialNumber])
VAR _MaxDate = 
    CALCULATE(
            MAX(FieldReplaceableUnit_productData[Date created]),
            FILTER(
                //ALL(FieldReplaceableUnit_productData),
                ALLEXCEPT(FieldReplaceableUnit_productData, FieldReplaceableUnit_productData[f_managedElementId], FieldReplaceableUnit_productData[f_fieldReplaceableUnitId]),
                FieldReplaceableUnit_productData[Date created] < MIN(FieldReplaceableUnit_productData[Date created])
            )
    )

VAR _Calculation = 
    CALCULATE(
        VALUES(FieldReplaceableUnit_productData[f_serialNumber]),
        FILTER(
            ALL(FieldReplaceableUnit_productData),
            FieldReplaceableUnit_productData[Date created] = _MaxDate && (FieldReplaceableUnit_productData[f_managedElementId] = SELECTEDVALUE(FieldReplaceableUnit_productData[f_managedElementId]) && FieldReplaceableUnit_productData[f_fieldReplaceableUnitId] = SELECTEDVALUE(FieldReplaceableUnit_productData[f_fieldReplaceableUnitId]))
            )
        )
VAR _Result = _Calculation

RETURN
    IF( ISBLANK(_Calculation), 0,
        IF(_SerialNumber = _Calculation,0,1)
    )

9 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Hi Anonymous,

    Please try this new measure and see if this solve your problem:

    ardware Swap =
VAR __var1 = 'FieldReplaceableUnit_productData'[f_serialNumber]
VAR __mostRecentDate =
    CALCULATE(
        MAX('FieldReplaceableUnit_productData'[Date created]),
        ALLEXCEPT('FieldReplaceableUnit_productData', 'FieldReplaceableUnit_productData'[managedElementId/fieldReplaceableUnitId])
    )
VAR __var2 =
    CALCULATE(
        VALUES('FieldReplaceableUnit_productData'[f_serialNumber]),
        'FieldReplaceableUnit_productData'[Date created] = __mostRecentDate,
        'FieldReplaceableUnit_productData'[managedElementId/fieldReplaceableUnitId] = EARLIER('FieldReplaceableUnit_productData'[managedElementId/fieldReplaceableUnitId])
    )
RETURN
    IF(ISBLANK(__var2), 0, IF(__var1 = __var2, 0, 1))
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi. Thanks for the feedback. I'm getting an error with equation provided, see below. Can you recommend a column equation as oppossed to a measure? The equation i mentioned previously was Column DAX

       

      A single value for column 'f_serialNumber' in table 'FieldReplaceableUnit_productData' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

      • _AAndrade's avatar
        _AAndrade
        Resident Rockstar
        2 years ago

        Can you share a sample data or a pbix file so I take a look?

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Anonymous If my post solved your problem, please Mark as solution and give a Kudos, I Will appreciate that.

    Thanks