Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'm using your run of the mill moving average DAX formula and noticed that filtered dates do not get excluded from the calculation
7 Day Avg =
CALCULATE(AVERAGEX (
DATESINPERIOD (
'Calendar'[Date],
LASTDATE ( 'Calendar'[Date] ),
-7,
DAY
),
[Staff #]
))
I'm assuming the is due to DATESINPERIOD not respecting filters, is there any way to fix that?
Filtering on any other column works as expected.
@firescape I didn't get the first few words of the first sentence, you should try to write you code like this:
7 Day Avg =
CALCULATE (
AVERAGEX ( VALUES ( 'Calendar'[Date] ), [Staff #] ),
DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -7, DAY )
)
I have also made a video on moving average, in case if you are interested, then check the first link after my signature.
I tried that formula and I'm still having the same issue, see below for an example.
Before filtering:
After filtering out January 11th notice how the average hasn't chaged for January 12th when it should be 175.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |