cancel
Showing results for
Did you mean:
Highlighted Helper II

## Calculate Mismatch Ageing

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Microsoft

## Re: Calculate Mismatch Ageing

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

Highlighted Microsoft

## Re: Calculate Mismatch Ageing

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

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries! Top Solution Authors
Top Kudoed Authors
Users online (1,041)