cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft v-jiascu-msft
Microsoft

Re: Moving Average Of Calculated Measure

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

 

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

View solution in original post

brekeke Frequent Visitor
Frequent Visitor

Re: Moving Average Of Calculated Measure

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

Microsoft v-jiascu-msft
Microsoft

Re: Moving Average Of Calculated Measure

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

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors