Forum Discussion
Moving Average function for Custom Calendar
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!
"
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])))
6 Replies
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
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/
- 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])))
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])))
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
