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 ...
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.
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.Getting the same result in the whole column is usually caused by a mis-wired data model. Check your filter directions.
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.