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

[kinseld5]

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

[_AAndrade]

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

Thanks 

[_AAndrade]

Hi @kinseld5,

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

[kinseld5]

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]

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

[kinseld5]

Sure, please see link below. You will notice that the hardware change on LK0191 was identified via the DAX equation sent on previously, but the one on DU0853 was not, because i'm missing data from the 29th Feb

https://drive.google.com/file/d/1CiqbhhnKhpuw4Nb75SmWbWo3DesHJud4/view?usp=drive_link

[_AAndrade]

@kinseld5  you need to give me access

[kinseld5]

Access granted

[_AAndrade · Accepted solution]

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

[kinseld5]

Thank you. This has worked. Really appreciate the help