Forum Discussion

brekeke's avatar
brekeke
Frequent Visitor
8 years ago
Solved

Moving Average Of Calculated Measure

Hi All,   I would like to calculate the moving average of an already calculated (% of grand total) measure. I've tried a lot of solutions but none of them seem to work correctly.   Table structur...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi brekeke,

     

    Please try this formula.

    Measure 2 =
VAR weekPeriods =
    CALCULATE (
        DISTINCTCOUNT ( tbl_periods_all[Period Column] ),
        DATESINPERIOD (
            'Calendar'[Date],
            MIN ( tbl_periods_all[Start Of Period] ),
            -21,
            DAY
        ),
        ALL ( tbl_periods_all[Period Column] )
    )
VAR pPeriods =
    CALCULATE (
        DISTINCTCOUNT ( tbl_periods_all[Period Column] ),
        DATESINPERIOD (
            'Calendar'[Date],
            MIN ( 'tbl_periods_all'[Start Of Period] ),
            -7,
            MONTH
        ),
        ALL ( tbl_periods_all[Period Column] )
    )
RETURN
    IF (
        MIN ( 'Period Selector'[period] ) = "Week",
        CALCULATE (
            SUM ( data[fact] ),
            DATESINPERIOD (
                'Calendar'[Date],
                MIN ( 'tbl_periods_all'[Start Of Period] ),
                -21,
                DAY
            ),
            ALL ( tbl_periods_all[Period Column] )
        )
            / weekPeriods,
        CALCULATE (
            SUM ( data[fact] ),
            DATESINPERIOD (
                'Calendar'[Date],
                MIN ( 'tbl_periods_all'[Start Of Period] ),
                -7,
                MONTH
            ),
            ALL ( tbl_periods_all[Period Column] )
        )
            / pPeriods
    )

    Moving_Average_Of_Calculated_Measure2

     

    Best Regards,

    Dale

     

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi brekeke,

 

It would be great if you can provide the DUMMY pbix file (Not your real data). I can add the solution to the file and send back to you. Then it could be easier for you to try the solution out.

 

Best Regards,

Dale

brekeke's avatar
brekeke
Frequent Visitor
8 years ago

Thanks v-jiascu-msft,

I'll try to make a dummy file, but it will take some time I guess.

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi brekeke,

       

      I made a few changes in your file. Please check it out here.

      The measure is:

      Measure =
IF (
    MIN ( 'Period Selector'[period] ) = "Week",
    CALCULATE (
        SUM ( data[fact] ),
        DATESINPERIOD (
            'Calendar'[Date],
            MIN ( 'tbl_periods_all'[Start Of Period] ),
            -21,
            DAY
        ),
        ALL ( tbl_periods_all[Period Column] )
    ),
    CALCULATE (
        SUM ( data[fact] ),
        DATESINPERIOD (
            'Calendar'[Date],
            MIN ( 'tbl_periods_all'[Start Of Period] ),
            -7,
            MONTH
        ),
        ALL ( tbl_periods_all[Period Column] )
    )
)

      Moving_Average_Of_Calculated_Measure

       

      Best Regards,

      Dale

      • brekeke's avatar
        brekeke
        Frequent Visitor
        8 years ago

        Hi v-jiascu-msft/Dale,

         

        This solution works for a rolling total, but it needs to be modified a bit for the moving average because if we divide the sum by 3 (I need the 3-period moving average), the first two periods will be wrong. So I will need to work on that a bit further.

         

        Thanks,

        brekeke