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,

 

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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

     

  • brekeke's avatar
    brekeke
    Frequent Visitor
    8 years ago

    Hi v-jiascu-msft,

     

    This works for me, except that for the first 2 periods (in case of a 3-period moving average), I would like to see zeros or blanks.

     

    On the other hand, I realized that I hadn't been that specific with my example. I will open another thread and link it here. Maybe you can provide with something for that challenge as well.

     

    Many thanks for your help,

    breki

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

    Hi brekeke,

     

    You are welcome. You can try this formula that will set the first two values to blanks. You can change the blue part to 0s.

    Measure 3 =
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",
        DIVIDE (
            CALCULATE (
                SUM ( data[fact] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    MIN ( 'tbl_periods_all'[Start Of Period] ),
                    -21,
                    DAY
                ),
                ALL ( tbl_periods_all[Period Column] )
            ),
            IF ( weekPeriods < 3, 0, weekPeriods ),
            BLANK ()
        ),
        DIVIDE (
            CALCULATE (
                SUM ( data[fact] ),
                DATESINPERIOD (
                    'Calendar'[Date],
                    MIN ( 'tbl_periods_all'[Start Of Period] ),
                    -7,
                    MONTH
                ),
                ALL ( tbl_periods_all[Period Column] )
            ),
            IF ( pPeriods < 3, 0, pPeriods ),
            BLANK ()
        )
    )

    Moving_Average_Of_Calculated_Measure3

     

    Best Regards,

    Dale