Calculate Mismatch Ageing

[apollo89]

Hello All,

Consider the following dataset

Row	Week	Field1	Field2	Value1	Value2	Week Age
1	202001	A	A	10	20	0
2	202002	A	A	10	20	1
3	202003	A	A	10	20	2
4	202004	A	A	10	10
5	202001	B	N/A	30		0
6	202002	B	N/A	30		1
7	202001	N/A	C		40	0
8	202002	N/A	C		40	1

I need to calculate the 'Week Age' column in PowerBI Desktop.

Scenario 1

For Rows 1, 2 and 3 there is a mismatch between Value 1 and value 2 for the two fields for the 3 weeks, hence the 'Week Age' should increment by 1 until there is a mismatch.

For row 4, there should be no 'Week Age' as the mismatch doesn't exist any more.

Scenario 2

For rows 5,6,7,8 there are cases where one of the fields has a missing value or 'N/A'. I would need to calculate 'Week Age' in this case as well.

Please let me know in case of any clarifications.

[v-yiruan-msft · Accepted solution]

Hi @apollo89 ,

You can create 2 calculated columns as below to achieve it:

1. Judge the value of Value1 and Value2 is equal or not: if equal, blank; otherwise, 1

Equal or not equal = IF('Week Age'[Value1]='Week Age'[Value2],BLANK(),1)

2. Create a calculated column to get week age based on the judge column

Week Age = 
IF (
    ISBLANK ( [Equal or not equal] ),
    BLANK (),
    CALCULATE (
        COUNT ( 'Week Age'[Equal or not equal] ),
        FILTER (
            'Week Age',
            'Week Age'[Equal or not equal] = 1
                && 'Week Age'[Row] <= EARLIER ( 'Week Age'[Row] )
                && 'Week Age'[Field1] = EARLIER ( 'Week Age'[Field1] )
        )
    ) - 1
)

Best Regards

Rena