Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 calc...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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

v-luwang-msft's avatar
v-luwang-msft
Community Support
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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é