Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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é
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
Hi @Anonymous ,
Try to change ALL( 'Tablename') in measure you used to be ALLSELECTED('Tablename').
Best Regards
Lucien
I have tried this:
Any ideas? Sorry for my stupid questions...
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é
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
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é
Please provide sample data in usable format (not as a picture) and show the expected outcome.
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é
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |