Moving average with parameters

[gumis_rulez]

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 

[SachinNandanwar · Accepted solution]

Its due to selection on this slicer 

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

[Anonymous · Accepted solution]

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.

[SachinNandanwar · Accepted solution]

Its due to selection on this slicer 

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