Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

6 Months MoM Average

Hi All,

 

I want to show 6 months MoM avergae in card for some catogeries.

See yellow highlighted cells.

 

 

I am trying to create dynamic DAX which can calculate MoM of last 6 Months and then avergae them.

For example: Sum(MoM% of last 6 month)/6

 

I am not able to achieve this calculation. Please help.

 

I have calculated MoM using below formulae:

Test_MoM% =

VAR Sum_CY = SUM ([Revenue])
 
VAR Sum_PY =
CALCULATE (
SUM ([Revenue]),
DATEADD([Date],-1,MONTH)
)
 
VAR MoM =
DIVIDE (Sum_CY, Sum_PY) - 1
 
Var Result =
IF(
MoM = -1,
BLANK(),
MoM
)
 
Return
Result
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please try the following formula:

     

    Test_MoM% = 
VAR Sum_CY = SUM ( 'Table'[Revenue] )
VAR Sum_PY =
CALCULATE (
    SUM ( 'Table'[Revenue] ),
    ALLEXCEPT ( 'Table', 'Table'[Row] ),
    DATEADD ( 'Table'[Date], -1, MONTH )
) 
VAR MoM =
DIVIDE ( Sum_CY, Sum_PY ) - 1 
Var Result =
IF (
    MoM = -1,
    BLANK (),
    MoM
)
Return Result
    Measure = 
var RowTotal =
AVERAGEX ( ALLSELECTED ( 'Table'[Month] ), [Test_MoM%] )

var ColumnTotal =
SUMX ( ALLSELECTED ( 'Table'[Row] ), [Test_MoM%] )

return 
IF (
    NOT ( HASONEVALUE ( 'Table'[Month] ) ) && NOT ( HASONEVALUE ( 'Table'[Row] ) ),
    SUMX ( ALLSELECTED ( 'Table' ), [Test_MoM%] ) / DISTINCTCOUNT ( 'Table'[Month] ),
    IF (
        HASONEVALUE ( 'Table'[Month] ) && NOT ( HASONEFILTER ( 'Table'[Row] ) ),
        SUMX ( ALLSELECTED ( 'Table'[Row] ), [Test_MoM%] ),
        AVERAGEX ( ALLSELECTED ('Table'[Month] ), [Test_MoM%] )
    )   
)

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Please try the following formula:

     

    Test_MoM% = 
VAR Sum_CY = SUM ( 'Table'[Revenue] )
VAR Sum_PY =
CALCULATE (
    SUM ( 'Table'[Revenue] ),
    ALLEXCEPT ( 'Table', 'Table'[Row] ),
    DATEADD ( 'Table'[Date], -1, MONTH )
) 
VAR MoM =
DIVIDE ( Sum_CY, Sum_PY ) - 1 
Var Result =
IF (
    MoM = -1,
    BLANK (),
    MoM
)
Return Result
    Measure = 
var RowTotal =
AVERAGEX ( ALLSELECTED ( 'Table'[Month] ), [Test_MoM%] )

var ColumnTotal =
SUMX ( ALLSELECTED ( 'Table'[Row] ), [Test_MoM%] )

return 
IF (
    NOT ( HASONEVALUE ( 'Table'[Month] ) ) && NOT ( HASONEVALUE ( 'Table'[Row] ) ),
    SUMX ( ALLSELECTED ( 'Table' ), [Test_MoM%] ) / DISTINCTCOUNT ( 'Table'[Month] ),
    IF (
        HASONEVALUE ( 'Table'[Month] ) && NOT ( HASONEFILTER ( 'Table'[Row] ) ),
        SUMX ( ALLSELECTED ( 'Table'[Row] ), [Test_MoM%] ),
        AVERAGEX ( ALLSELECTED ('Table'[Month] ), [Test_MoM%] )
    )   
)

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks a lot 🙂

       

      It's working for no. of months I want to show average.

       

      Regards,
      Sahil Adya

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Use AVERAGEX().

    Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) .