topic Re: Moving average with parameters in DAX Commands and Tips

Moving average with parameters

gumis_rulez — Tue, 20 Aug 2024 10:08:30 GMT

Hi,

I am trying to calculate moving average which time frame will be affected by choosen month. If I choose May, the Montly Sales Chart is showing data up to May. Would like to achieve also with moving average. Can anybody help me how to make it?

Moving Average Quantity (Months Table) = VAR ChoosenMonth = Months[Month Value] Var ChoosenYear = SELECTEDVALUE('Calendar'[Year_ID]) VAR AverageDay = [Selected Moving Average] VAR LastTransactionDate = MAX('Calendar'[Transaction_Date]) VAR PeriodInVisual = FILTER( ALL('Calendar'[Transaction_Date]), 'Calendar'[Transaction_Date] > LastTransactionDate - AverageDay && 'Calendar'[Transaction_Date] <= LastTransactionDate ) VAR Result = CALCULATE( [Average Quantity (Months Table)], PeriodInVisual ) RETURN Result

File:

https://drive.google.com/file/d/19l4_3RTl2TfXW8vNQ_Usq45j912IYNua/view?usp=sharing

Re: Moving average with parameters

SachinNandanwar — Tue, 20 Aug 2024 22:47:57 GMT

Its due to selection on this slicer

So its basically plotting averages X months ahead of a given month

Re: Moving average with parameters

Anonymous — Wed, 21 Aug 2024 09:15:30 GMT

Hi All,
Firstly SachinNandanwar thank you for your solution!
And gumis_rulez to my understanding,you want to align the month in which Moving Average Quantity occurs with the Average Quantity value, your DAX statement is working, the problem is mainly in the field, we can add some filters to our visual object to achieve what you need.

a = IF( [Average Quantity (Months Table)]<>BLANK(), 1, 0)

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.