Moving Average by Filtering Specific Column

[Anonymous]

Hi All,
Please note that  I am able to get the Last 7 days rolling Average for the below chart by Using the following Dax

Last 7 Days AVG = ([7 Days Moving SUM.]+0)/[# of Days]

7 Days Moving SUM. = CALCULATE([Total Fuel Consumed],DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-7,DAY))

# of Days = CALCULATE(DISTINCTCOUNT('Calendar'[Date]),DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-7,DAY))

Anyway, If I Filter the above chart by Specific Column then Last 7 Days AVG getting wrong

In this case, the Last 7 Days' AVG should be about 350/7,350/6,... so on

So how can I solve this?

Hoping for your support Guys...

Thanking a lot

Faithfully,

Rifdhy (+94770848216)

[liuqi_pbi · Accepted solution]

Hi @Anonymous 

How about try these measures 

7 Days Moving SUM. =
CALCULATE (
    [Total Fuel Consumed],
    ALL ( 'Calendar'[Date] ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -7, DAY )
)

# of Days =
CALCULATE (
    DISTINCTCOUNT ( 'Calendar'[Date] ),
    ALL ( 'Calendar'[Date] ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -7, DAY )
)

Last 7 Days AVG = DIVIDE ( [7 Days Moving SUM.] + 0, [# of Days] )

In addition, did you use 'Calendar'[Date] column on X-axis in the visual? It is expected there. 

----------------------------------------------------------------------

If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!