Rolling Average In PowerBI Exclude Current Month

Question

I have created a rolling average of total sales for the past 3 months. I want it to exclude the current month. I think my answer may be to combine the following measure I wrote with the following logic but I am having some challenges making it work. Could you anyone assist?

Again my goal is to exclude the current month. An example would be for this month calculate Monthly Average of Oct-Dec of 2019

Moving AVG Measure:

Moving X Months AVG = SUMX(DATESINPERIOD(DSS_DATA[Run_Date],LASTDATE(DSS_DATA[Run_Date]),-3,MONTH),[Total Internal Samples])/3

VAR LastDayofPrevMonth = DATEADD(STARTOFMONTH('Calendar'[Date]), -1, DAY) 
VAR FirstDayofLast3Month = DATEADD(STARTOFMONTH(LastDayofPrevMonth), -2, MONTH)

Any help would be much appreciated

CR7SMS CR7SMS · Accepted Answer · 2020-01-21T18:07:00

You can create the start and end date variable using TODAY fubnction and use that as a filter to calculate the running average:

Column 2 = 
    VAR Start1 = 
            DATE(
                IF(MONTH(TODAY())<=3,YEAR(TODAY())-1,YEAR(TODAY())),
                IF(MONTH(TODAY())=1,12,IF(MONTH(TODAY())=2,11,IF(MONTH(TODAY())=3,10,MONTH(TODAY())-1))),
                1)
    VAR End1 = 
            DATE(
                YEAR(TODAY()),
                MONTH(TODAY()),
                1)-1
    VAR Average1 = 
            CALCULATE(
                AVERAGE(DSS_Data[Total Internal Samples]),
                FILTER(DSS_Data,DSS_DATA[Run_date]>=Start1 && DSS_Data[Run_date]<=End1))
RETURN Average1

Rolling Average In PowerBI Exclude Current Month

1 Answers