Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @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)

vluwangmsft_0-1627629298952.png

 

 

Best Regards

Lucien

View solution in original post

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

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

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

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

Anonymous
Not applicable

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 @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)

vluwangmsft_0-1627629298952.png

 

 

Best Regards

Lucien

Anonymous
Not applicable

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

HerveRC_2020_0-1627638100586.png

Thanks.

Hervé

Hi @Anonymous ,

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

 

 

Best Regards

Lucien

Anonymous
Not applicable

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

Anonymous
Not applicable

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é

Anonymous
Not applicable

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

v-luwang-msft
Community Support
Community Support

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
Not applicable

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.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.