Forum Discussion
Dynamic Calculation of Average
I want to create a table displaying the last moving 3-month (Oct-Nov-Dec 2022) income statement object, e.g. Cost of Sales. I want to display the following:
- 4th column --> the average of the last 3 month.
- 5th column --> Cost of sales for the current month (Jan 2023)
- 6th column --> the forecast for Jan 2023 -- this will be fetched from a lookup data table
- 7th column --> show the difference (actual Jan 2023 vs. forecast Jan 2023
- 8th column --> the budget for Jan 2023 -- this will be fetched from a lookup data table
- 9th column --> show the difference (actual Jan 2023 vs budget Jan 2023
- 10th column --> same data as the 5th column
- 11th column --> show the difference same month last year (PY - previous year)-- this will be fetched from the existing data table (2022 data)
- 12th column --> show the difference of the Jan 2023 from the 3 month's moving average (for this case, it is Oct-Nov-Dec 2022)
This is the Excel table t be replicated -->
Thanks...after checking the relationships and DAX formula, dynamic calculation of the 3-months data are now working-->
Three-months rolling average = -(CALCULATE(SUM('FS_Conso_TB_2022 (2)'[Adjusted Balance]),DATESINPERIOD('FS_Conso_TB_2022 (2)'[Month],LASTDATE('FS_Conso_TB_2022 (2)'[Month]),-3,MONTH))/3)/1000000The relationship [Month} should be one-to-one so the slicer will work as well when selected which month to display.
6 Replies
- lbendlin
Super User
Ok - What have you tried and where are you stuck?
I initially tried using the following DAX calculation -->
(CALCULATE([Adj Balance],DATESINPERIOD('FS_Conso_TB_2022 (2)'[Month],LASTDATE('FS_Conso_TB_2022 (2)'[Month]),-3,MONTH))/30/1000) based on Calendar table where Year-Qtr are represented in the slicer. However, even if I choose any quarter, it will show the same results, which is wrong.- lbendlin
Getting the same result in the whole column is usually caused by a mis-wired data model. Check your filter directions.
