Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
"
Solved! Go to Solution.
Sorry, my bad
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
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/
Sorry, my bad
You are amazing, good will come to you, good sir. But why is it -5 and not -6. sorry for the beginner question. :):)
@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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |