Skip to main content
cancel
Showing results for 
Search instead 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

Reply
emilio-jgsoc
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 -->

 

L3M-Sample.png

1 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.
emiliojgsoc_0-1680169315678.png

 

View solution in original post

6 REPLIES 6
lbendlin
Super User
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.L3M_Dax Calculation.jpg

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

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 

anushaghi123_0-1693309933642.png

 

 

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

 

lbendlin_0-1693319367158.png

 

 

You can only do that in Power Query via List.Accumulate.  But then you would lose your +/- 20% modeling capabilities.

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.
emiliojgsoc_0-1680169315678.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.