DAX to get moving average/sum with month without data

[Anonymous]

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:

I appreciate your help with this matter.

[vivran22 · Accepted solution]

Hello @Anonymous 

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

[v-robertq-msft · Accepted solution]

Hello @Anonymous 

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

[Ashish_Mathur]

Hi,

Try this measure

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

Hope this helps.

[vivran22 · Accepted solution]

Hello @Anonymous 

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