Displaying results for Moving Average

[vivran22]

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

[Greg_Deckler]

@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

[vivran22]

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

[Greg_Deckler · Accepted solution]

@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