cancel
Showing results for
Did you mean:
Microsoft

## Re: Moving Average Of Calculated Measure

Hi @brekeke,

```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
)```

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.
Frequent Visitor

## Re: Moving Average Of Calculated Measure

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.

breki

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 ()
)
)```

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.

Announcements

#### 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?

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

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