cancel
Showing results for
Did you mean:  Helper III

## MAT Calculation

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)

"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é
1 ACCEPTED SOLUTION  Community Support

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

13 REPLIES 13  Community Support

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  Community Support

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  Helper III

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é  Community Support

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  Helper III

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

Hervé  Community Support

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

Best Regards

Lucien  Helper III

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...  Helper III

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é  Helper III  Community Support

(% 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  Helper III

Dear @v-luwang-msft

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é  Super User

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

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é  