topic Re: Displaying results for Moving Average in DAX Commands and Tips

Displaying results for Moving Average

vivran22 — Sun, 06 Sep 2020 05:55:56 GMT

Hello Community members,

I am using the following measure for calculating a monthly moving average:

Moving Average _M = VAR _CurrentDate = MIN(ftCalendar[Date])-1 VAR _FilterDate = DATESINPERIOD(ftCalendar[Date],_CurrentDate,-3,MONTH) VAR _Monthly = CALCULATE([Revenue],_FilterDate) VAR _MonthCount = CALCULATE(DISTINCTCOUNT(ftCalendar[Month]),_FilterDate) VAR _Average = DIVIDE(_Monthly,_MonthCount) RETURN _Average

How can I:

a) omit the moving average values where Revenue for current month is blank

b) show moving average till the point I have 3 months historical values. In the example above, the moving average should show value for January 2020.

Appreciate your support

Cheers!

Vivek

Re: Displaying results for Moving Average

Greg_Deckler — Sun, 06 Sep 2020 06:08:34 GMT

vivran22 - It's really difficult to decipher this without sample source data but I will try to make some suggestions

Moving Average _M = VAR _CurrentDate = MIN(ftCalendar[Date])-1 //GJD VAR _PreviousMonth = EOMONTH(_CurrentDate,-1) VAR _MonthStart = DATE(YEAR(_CurrentDate),MONTH(_CurrentDate),1) VAR _MonthEnd = EOMONTH(_CurrentDate,0) VAR _CurrentRevenue = CALCULATE([Revenue],FILTER(ftCalendar[Date]>=_MonthStart && ftCalendar[Date]<=_MonthEnd)) VAR _PreviousMonthStart = DATE(YEAR(_PreviousMonth),MONTH(_PreviousMonth),1) VAR _PreviousMonthEnd = EOMONTH(_PreviousMonth,0) VAR _PreviousRevenue = CALCULATE([Revenue],FILTER(ftCalendar[Date]>=_PreviousMonthStart && ftCalendar[Date]<=_PreviousMonthEnd)) //GJD VAR _FilterDate = DATESINPERIOD(ftCalendar[Date],_CurrentDate,-3,MONTH) VAR _Monthly = CALCULATE([Revenue],_FilterDate) VAR _MonthCount = CALCULATE(DISTINCTCOUNT(ftCalendar[Month]),_FilterDate) VAR _Average = DIVIDE(_Monthly,_MonthCount) RETURN //GJD SWITCH(TRUE(), NOT(ISBLANK(_CurrentRevenue)),_Average, NOT(ISBLANK(_PreviousRevenue)),_Average, BLANK() ) //GJD //_Average

Re: Displaying results for Moving Average

vivran22 — Sun, 06 Sep 2020 06:21:32 GMT

Thanks Greg_Deckler for your prompt response.

I have used your measure suggested by you (with minor required modifications) and it is returning blank:

Enclosing the pbix file for your reference.

Thanks!

Vivek

Re: Displaying results for Moving Average

Greg_Deckler — Sun, 06 Sep 2020 06:33:16 GMT

vivran22 - Couple minor things. One, didn't realize you were subtracting a da from "_CurrentDate". Second, missed an ALL.

Updated PBIX attached.

Moving Average _M2 = VAR _CurrentDate = MIN ( ftCalendar[Date] ) - 1 //GJD VAR _MyCurrentDate = MIN(ftCalendar[Date]) VAR _PreviousMonth = EOMONTH ( _MyCurrentDate, -1 ) VAR _MonthStart = DATE ( YEAR ( _MyCurrentDate ), MONTH ( _MyCurrentDate ), 1 ) VAR _MonthEnd = EOMONTH ( _MyCurrentDate, 0 ) VAR _CurrentRevenue = CALCULATE ( [Revenue], FILTER ( ftCalendar, ftCalendar[Date] >= _MonthStart && ftCalendar[Date] <= _MonthEnd ) ) VAR _PreviousMonthStart = DATE ( YEAR ( _PreviousMonth ), MONTH ( _PreviousMonth ), 1 ) VAR _PreviousMonthEnd = EOMONTH ( _PreviousMonth, 0 ) VAR _PreviousRevenue = CALCULATE ( [Revenue], FILTER ( ALL(ftCalendar), ftCalendar[Date] >= _PreviousMonthStart && ftCalendar[Date] <= _PreviousMonthEnd ) ) //GJD VAR _FilterDate = DATESINPERIOD ( ftCalendar[Date], _CurrentDate, -3, MONTH ) VAR _Monthly = CALCULATE ( [Revenue], _FilterDate ) VAR _MonthCount = CALCULATE ( DISTINCTCOUNT ( ftCalendar[Month] ), _FilterDate ) VAR _Average = DIVIDE ( _Monthly, _MonthCount ) RETURN SWITCH ( TRUE (), NOT ( ISBLANK ( _CurrentRevenue ) ), _Average, NOT ( ISBLANK ( _PreviousRevenue ) ), _Average, BLANK () ) //_Average