Forum Discussion

gumis_rulez's avatar
gumis_rulez
Icon for Helper I rankHelper I
2 years ago
Solved

Moving average with parameters

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's avatar
    SachinNandanwar
    2 years ago

    Its due to selection on this slicer 

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

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    Its due to selection on this slicer 

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.