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 ,

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Thanks.

Hervé

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

    Hi Anonymous ,

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

     

     

    Best Regards

    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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