Dynamic Calculation of Average

[emilio-jgsoc]

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 -->

[lbendlin]

Ok - What have you tried and where are you stuck?

[emilio-jgsoc]

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.

[anushaghi123]

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.

[lbendlin]

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.

[emilio-jgsoc · Accepted solution]

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)/1000000

The relationship [Month} should be one-to-one so the slicer will work as well when selected which month to display.