12 Month Rolling Average (Fiscal Calendar) Calculation
Hi, I'm trying to recreate the following formula from Excel in DAX: The 12 month rolling avergae formula is: =SUM(E81:E133)/SUM(D81:D133)*100000 =SUM(E82:E134)/SUM(D82:D134)*100000 =SUM(E83:E135)/SUM(D83:D135)*100000 Column E is Total X Column D is Total Y So it's basically summing the last 53 rows of the 'Total X' column divided by last 53 rows of 'Total Y' column * 100000. I'm using the company calendar which is fiscal periods so cannot use any of the inbuilt date/time intelligence functions or the quick measures. My calendar looks like this: Please could someone explain how I can create the same formula in dax...It's driving me mad!
Calculate sum Revenue for the same fiscal Period last year
I have a period(1,2,3,4,5,6,7,8,9,10,11,12) slicer and year(2018,2019,2020) and will like to calculate the sum of revenue for last year for the same Period that the user pick Vs the Revenue this year for that period. Now using sameperiodaslast year function won't work because I am using fiscal periods and the start date/end date of the same period is not the same. If i use year -1 it gets me the result I am looking for but when I select all periods it does a full year vs this year revenue stops at todays number. Any idea how to stop last year revenue in the same day as today -1 year.
