cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HerveRC_2020
Helper III
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)

 

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

Hi @HerveRC_2020 ,

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)

vluwangmsft_0-1627629298952.png

 

 

Best Regards

Lucien

View solution in original post

13 REPLIES 13
v-luwang-msft
Community Support
Community Support

Hi @HerveRC_2020 ,

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

Hi @HerveRC_2020 ,

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

vluwangmsft_0-1627612616995.png(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?

HerveRC_2020_0-1627628240796.png

Thanks

Hervé

 

 

Hi @HerveRC_2020 ,

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)

vluwangmsft_0-1627629298952.png

 

 

Best Regards

Lucien

View solution in original post

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

HerveRC_2020_0-1627638100586.png

Thanks.

Hervé

Hi @HerveRC_2020 ,

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

 

 

Best Regards

Lucien

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:
HerveRC_2020_0-1627639035142.png

Any ideas? Sorry for my stupid questions...

Hello Lucien,

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

HerveRC_2020_0-1627634929591.png

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

Thanks

Hervé

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

v-luwang-msft
Community Support
Community Support

Hi @HerveRC_2020 ,


(% 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é

lbendlin
Super User
Super User

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

Hello @lbendlin 

Here is the table:

 

YearCONFORMITYPeriodFactories
2020OKP13RCA
2020OKP13RCA
2020OKP13RCA
2021OKP01RFT
2021WRNG_NOKP01RFT
2021OKP02RNI
2021OKP02RNI
2021OKP02RNI
2021OKP02RNI
2021OKP02RNI

 

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é

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!