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,

 

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