Moving Average function for Custom Calendar

[Anonymous]

Hi all, 

I have a custom calendar running on my report.  For. e.g., in the picture, month start for feb is actually the 28th of Jan. The issue here is that when i try to calculate the moving average for the last 6 months using the following function (see below), i do not get the full month result that also includes the 28th of jan. I feel that it is because of the MONTH function that only calculates from the 1st of feb. Does anyone have any suggestions on how to solve this? Would be eternally grateful! 

"

Total project hours last 6 months = Calculate(sum('Project hours - Project Category & Area'[Project Hours]),DATESINPERIOD('Calendar document date'[Calendar Document Date.Date],MAX('Calendar document date'[Calendar Document Date.Date]),-6,month))"

Thanks alot!!!

[MattAllington]

It's the n-1 issue that occurs with inclusive dates. If it's now March and you want 2 months of data  you need month 3 -1 to get Feb, not month 3-2

[MattAllington]

You cannot use the inbuilt time intelligence functions (such as DATESINPERIOD) for a non standard calendar.  I recommend you add a monthID column to your calendar. Start at 1 for the first month and then increment by 1 for every new month, never restarting. This gives you a hook to write custom time intelligence functions. A rolling 6 month total would be something like this

Total project hours last 6 months = Calculate(sum('Project hours - Project Category & Area'[Project Hours]),filter(all('Calendar document date'),Calendar Document Date[MonthID]>= MAX('Calendar document date'[MonthID])-5 && Calendar Document Date[MonthID]<= MAX('Calendar document date'[MonthID])))

read my article here for more of an explanation https://exceleratorbi.com.au/dax-time-intelligence-beginners/

[Anonymous]

Thanks alot for the help!! But for some reason it is not working.. created the index column and input the formula. Something is missing..

last 6 months rolling = Calculate('Project hours - Project Category & Area'[Project Hours.],filter(all('Calendar document date'),'Calendar document date'[Index]>= 'Calendar document date'[Index]-5 && 'Calendar document date'[Index]<= MAX('Calendar document date'[Index])))

[MattAllington · Accepted solution]

Sorry, my bad

last 6 months rolling = Calculate('Project hours - Project Category & Area'[Project Hours.],filter(all('Calendar document date'),'Calendar document date'[Index]>= max('Calendar document date'[Index])-5 && 'Calendar document date'[Index]<= MAX('Calendar document date'[Index])))

[Anonymous]

You are amazing, good will come to you, good sir. But why is it -5 and not -6. sorry for the beginner question. :):) 

[amitchandak]

@Anonymous , Try formula like

Rolling 6 = divide( CALCULATE(sum('Project hours - Project Category & Area'[Project Hours]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),0),-5,Day)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),0),-6,Day), filter(Sales,not(isblank(sum('Project hours - Project Category & Area'[Project Hours]))))))

I added divide by number of month, you can remove that if needed