cancel
Showing results for
Did you mean:
Highlighted
Helper I

## DAX to get moving average/sum with month without data

Hi there!

I did some searches over the PBI Community but I didn't anything how can I calculate moving average/sum with certain months with no data.

I need something like as below:

 Date Sum of events Mo Sum of events MovAvg 6 Mo 01-03-2020 1 1 01-04-2020 zero events (no data) 1 01-05-2020 2 3 01-06-2020 zero events (no data) 3 01-07-2020 1 4 01-08-2020 6 10 01-09-2020 2 11

Note: the blue line above just show you an example that I need for the next rows.

I am using this DAX:

Events 6 MO =
AVERAGEX(
DATESINPERIOD( dCalendario[Data], LASTDATE( dCalendario[Data] ), -5, MONTH ),
[Sum of Events (All)])

... with this chart result:

3 REPLIES 3
Highlighted
Super User II

## Re: DAX to get moving average/sum with month without data

Hello @marcospaula

Is this what you are looking for?

If yes, then you may use the following measure:

``````Runnning Sum =
VAR _CurrentDate = MAX(ftCalendar[Date])
VAR  _StartDate = EOMONTH(_CurrentDate,-7)+1
VAR _Filter =
FILTER(
All(ftCalendar[Date]),
ftCalendar[Date] >= _StartDate
&& ftCalendar[Date] <=_CurrentDate
)
VAR _Sum =
CALCULATE(SUM('Table'[Sum of events Mo]),_Filter)
RETURN
_Sum``````

If not, then please share more details in terms of expected output.

Cheers!
Vivek

Blog: vivran.in/my-blog

Kudos will be cherry on the top 🙂

Proud to be a Super User!

Highlighted
Super User V

## Re: DAX to get moving average/sum with month without data

Hi,

Try this measure

=calculate([Sum of Events (All)],datesbetween(dCalendario[Data],edate(min(dCalendario[Data]),-5),max(dCalendario[Data])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Community Support

## Re: DAX to get moving average/sum with month without data

Hello @marcospaula

According to the sample table you gave,I think that the [Sum of events MovAvg 6 Mo] value of the blue line should be 12,you should check if you enter the wrong value. if it's 12,you can try the following measure:

``````Events 6 MO =
CALCULATE
(SUM('Table'[Sum of events Mo])
,FILTER(ALLSELECTED('Table')
,'Table'[Date]<=MAX('Table'[Date])))``````

and you can get the output chart like this:

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

Best Regards,

Robert Qin

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021