Forum Discussion
Dynamic Calculation of Average
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 -->
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.Hi, Need help with the following question
Each product has data, for that we have to create data as a moving average of the last 4 months
For example: United States 37101 April, May, June, July are 1683,1668,776,1885. Predict Aug as Average of the 4, then use average of May, June July, Aug as September. And a slider that can be used to do -20% to +20%. Where we cn check forecast in case we go -1% of the moving average or 5% of the moving average. I have created a slider using New Parameter
Dax Formula (Quick Measure) - Product_Count rolling average =
IF(
ISFILTERED('Table_name'[dimdate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table_name'[dimdate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table_name'),
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Table_name'[Product_Count]),
ALL('Table_name'[dimdate].[Day])
)
)
)Could you please help.
Thanks.
This is not something you can do in DAX as it introduces a circular reference. (You cannot write the results of a measure back into a column, like the 1503 in the example that would have to go back into the value column).
You can only do that in Power Query via List.Accumulate. But then you would lose your +/- 20% modeling capabilities.
