cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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:

 

DateSum of events MoSum of events MovAvg 6 Mo
01-03-202011
01-04-2020zero events (no data)1
01-05-202023
01-06-2020zero events (no data)3
01-07-202014
01-08-2020610
01-09-2020211

 

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:
 
Capture.PNG

 

I appreciate your help with this matter.

 

 

 

3 REPLIES 3
Highlighted
Super User II
Super User II

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

Hello @marcospaula 

 

Is this what you are looking for?

 

image.png

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
Connect on LinkedIn
Follow on Twitter





Did I answer your question? Mark my post as a solution!

Kudos will be cherry on the top 🙂


Proud to be a Super User!




Highlighted
Super User V
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
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Support
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:

v-robertq-msft_0-1601435737810.png

 

If not,please share more details to get your expected output.

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors