MAT Calculation

[Anonymous]

Dear Power BI Community,

I would like to calculate a MAT using a DAX formula but I would need you help.

MAT calculation would be from now (year 2021) over the last 13 periods and would like to calculate like this on % Grade A:

MAT = (% Grade A in Period 8 2021 (we are in P8) - % Grade A in P7 2021) + (% Grade A in P7 2021- % Grade A in P6 2021).... + (% Grade A in P9 2020 - % Grade A in P8 2020)

The % Grade A is already a formula

"Percentage = DIVIDE(CALCULATE(

COUNTA('rclms_qa'[CONFORMITY]),

ALLSELECTED('rclms_qa'[CONFORMITY])

),CALCULATE(

COUNTA('rclms_qa'[CONFORMITY]),

ALL('rclms_qa'[CONFORMITY])

))*100"

Do you have any recommendations? I would appreciate.

Thanks

Hervé

[v-luwang-msft]

Hi @Anonymous ,

I don't see the fields in the slicer in the data you provided earlier, is it convenient to provide the pbix file, remember to delete the confidential data.

Best Regards

Lucien

[v-luwang-msft]

Hi @Anonymous ,

Try the following measure:

Percentage22 = 
DIVIDE (
    (CALCULATE (
        COUNTA ( 'rclms_qa'[CONFORMITY] ),
        FILTER (
            ALL(rclms_qa),
            rclms_qa[Period] = MAX ( rclms_qa[Period] )
                && rclms_qa[Year] = MAX ( rclms_qa[Year] )
        )
    )
        - CALCULATE (
            COUNTA ( 'rclms_qa'[CONFORMITY] ),
            FILTER (
                ALL(rclms_qa),
                rclms_qa[Period]
                    = IF (
                        INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) < 10
                            || INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) = 14,
                        "P" & "0"
                            & IF (
                                INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) = 14,
                                1,
                                INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 )
                            ),
                        "P"
                            & INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 )
                    )
                    && rclms_qa[Year]
                        = MAX ( rclms_qa[Year] ) - 1
            )
        )),
    CALCULATE ( COUNTA ( 'rclms_qa'[CONFORMITY] ), ALL ( 'rclms_qa') )
) * 100

(the picture (3-1)/14=14.29)

This measure, will automatically calculate the difference between the date in the column and the date before the 13 stages, and then divided by the total, I provide my PBIX, which has some of my thinking and verification steps, you can refer to, I hope it will be useful to you.

Best Regards

Lucien

[Anonymous]

Dear @v-luwang-msft 

Thanks a lot - I'm really impressed by the work & formula.

I have one doubt regarding the calculation because if we are in P13 2020, 13 periods earlier, we should be in P1 2020 (and not P1 2019) -> no?

Thanks

Hervé

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Sorry, it was an oversight on my part and I have adjusted the relevant measure😋:

Percentage22 = 
DIVIDE (
    (CALCULATE (
        COUNTA ( 'rclms_qa'[CONFORMITY] ),
        FILTER (
            ALL(rclms_qa),
            rclms_qa[Period] = MAX ( rclms_qa[Period] )
                && rclms_qa[Year] = MAX ( rclms_qa[Year] )
        )
    )
        - CALCULATE (
            COUNTA ( 'rclms_qa'[CONFORMITY] ),
            FILTER (
                ALL(rclms_qa),
                rclms_qa[Period]
                    = IF (
                        INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) < 10
                            || INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) = 14,
                        "P" & "0"
                            & IF (
                                INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) = 14,
                                1,
                                INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 )
                            ),
                        "P"
                            & INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 )
                    )
                    && rclms_qa[Year]
                        =IF (
                        INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ))) =13, MAX ( rclms_qa[Year] ), MAX ( rclms_qa[Year] ) - 1)
            )
        )),
    CALCULATE ( COUNTA ( 'rclms_qa'[CONFORMITY] ), ALL ( 'rclms_qa') )
) * 100

YEARTEST = IF (
                        INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) )  )=13, MAX ( rclms_qa[Year] ), MAX ( rclms_qa[Year] ) - 1)

Best Regards

Lucien

[Anonymous]

@v-luwang-msft do you know why the slicers are not responding?

Thanks.

Hervé

[v-luwang-msft]

Hi @Anonymous ,

Try to change  ALL( 'Tablename') in measure you used to be   ALLSELECTED('Tablename').

Best Regards

Lucien

[Anonymous]

I have tried this:

MAT = DIVIDE (

(CALCULATE (

COUNTA ( 'rclms_qa'[CONFORMITY] ),

FILTER (

ALLSELECTED(rclms_qa),

rclms_qa[Period] = MAX ( rclms_qa[Period] )

&& rclms_qa[Mars Year] = MAX ( rclms_qa[Mars Year] )

)

)

- CALCULATE (

COUNTA ( 'rclms_qa'[CONFORMITY] ),

FILTER (

ALLSELECTED(rclms_qa),

rclms_qa[Period]

= IF (

INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) < 10

|| INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) = 14,

"P" & "0"

& IF (

INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 ) = 14,

1,

INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 )

),

"P"

& INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ) ) + 1 )

)

&& rclms_qa[Mars Year]

=IF (

INT ( VALUE ( RIGHT ( MAX ( rclms_qa[Period] ), 2 ))) =13, MAX ( rclms_qa[Mars Year] ), MAX ( rclms_qa[Mars Year] ) - 1)

)

)),

CALCULATE ( COUNTA ( 'rclms_qa'[CONFORMITY] ), ALLSELECTED( ( 'rclms_qa') )

) * 100)

But results are strange - almost 0:

Any ideas? Sorry for my stupid questions...

[Anonymous]

Hello Lucien,

I'm sorry - I have another question. I was trying to make a graph/year:

We are in P08 2021 -> why it's stopped at P05?

Thanks

Hervé

[Anonymous]

@v-luwang-msft  my bad, I have the answer. Thanks Lucien

[v-luwang-msft]

Hi @Anonymous ,

(% Grade A in Period 8 2021- % Grade A in Period 7 2021)+(% Grade A in Period 7 2021-% Grade A in Period 6 2021)

=% Grade A in Period 8 2021- % Grade A in Period 7 2021+% Grade A in Period 7 2021-% Grade A in Period 6 2021

=% Grade A in Period 8 2021-% Grade A in Period 6 2021

If you follow the calculation steps you mentioned:

final is % Grade A in Period 8 2021 -% Grade A in P8 2020.

Then   (COUNTA ( 'rclms_qa'[CONFORMITY] )  and filter year= 2021,period=8 ) -( (COUNTA ( 'rclms_qa'[CONFORMITY] )  and filter year= 2020,period=8 )/COUNTA ( 'rclms_qa'[CONFORMITY] ) all the table .

Percentage =
DIVIDE (
    CALCULATE (
        COUNTA ( 'rclms_qa'[CONFORMITY] ),
        FILTER ( rclms_qa, rclms_qa[Period] = "P08" && rclms_qa[Year] = 2021 )
    )
        - CALCULATE (
            COUNTA ( 'rclms_qa'[CONFORMITY] ),
            FILTER ( rclms_qa, rclms_qa[Period] = "P08" && rclms_qa[Year] = 2020 )
        ),
    CALCULATE ( COUNTA ( 'rclms_qa'[CONFORMITY] ), ALL ( 'rclms_qa'[CONFORMITY] ) )
) * 100

And the data you provided ,try the following dax:

Percentage = 
 DIVIDE (  CALCULATE (
        COUNTA ( 'rclms_qa'[CONFORMITY] ),
        FILTER(rclms_qa,rclms_qa[Period]="P02"&&rclms_qa[Year]=2021)
    )- CALCULATE (
        COUNTA ( 'rclms_qa'[CONFORMITY] ),
        FILTER(rclms_qa,rclms_qa[Period]="P13"&&rclms_qa[Year]=2020)
    ) ,CALCULATE ( COUNTA ( 'rclms_qa'[CONFORMITY] ), ALL ( 'rclms_qa'[CONFORMITY] ) )
) * 100

If you still have any doubts, you can share your pbix file and remember to remove confidential data.

WIsh it is helpful for you!

Best Regards

Lucien

[Anonymous]

Dear @v-luwang-msft 

Thanks for your answer. Your calculation seems logical.

How can we make it automatic? I mean I would need to take the actual period (or the day of today that corresponds to a certain period (ex. 29/07/21 = P8) and calculate on the over last 13 periods (from P8 2021 until P9 2020 = 13 periods = 1 year for our company)?

Hervé

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[Anonymous]

Hello @lbendlin 

Here is the table:

Year	CONFORMITY	Period	Factories
2020	OK	P13	RCA
2020	OK	P13	RCA
2020	OK	P13	RCA
2021	OK	P01	RFT
2021	WRNG_NOK	P01	RFT
2021	OK	P02	RNI
2021	OK	P02	RNI
2021	OK	P02	RNI
2021	OK	P02	RNI
2021	OK	P02	RNI

So the Grade A (control OK) will be calculated based on the formula above - then I need to make the % Grade A difference (P2-P1 2021) + (P1 2021-P13 2020).

Outcome would be + or - x %.

Thanks for your help - and let me know if not clear.

Hervé