Forum Discussion

apollo89's avatar
apollo89
Helper II
6 years ago
Solved

Calculate Mismatch Ageing

Hello All,

 

Consider the following dataset

 

RowWeek  Field1 Field2 Value1 Value2 Week Age
1202001  A  A  10  20  0
2202002  A  A  10  20  1
3202003  A  A  10  20  2
4202004  A  A  10  10 
5202001  B  N/A  30   0
6202002  B  N/A  30   1
7202001  N/A  C   40  0
8202002  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.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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