Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Moving Average function for Custom Calendar

Capture.JPG

 

 

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!!!
1 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])))


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

6 REPLIES 6

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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/

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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])))


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.