cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

1 ACCEPTED SOLUTION
New Member

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.

6 REPLIES 6
Super User

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

New Member

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.
Super User

Getting the same result in the whole column is usually caused by a mis-wired data model. Check your filter directions.

Helper II

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

Thanks.

Super User

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.

New Member

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors