Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Might be overthinking this one. I have a 30 day rolling average formula as this:
Hi @seanpratt ,
I created a flag column to check if it is weekday and created a rank column to calculate 30 weekdays.
Then create a measure to get the rolling average.
AVG30 =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[flag] = 1
&& 'Table'[rank] <= SELECTEDVALUE ( 'Table'[rank] )
&& 'Table'[rank]
>= SELECTEDVALUE ( 'Table'[rank] ) - 30
)
) / 30
Here is the test file for your reference.
That's not quite it either. I do have a separate Calendar table so wondering if that's creating a disconnect. I've attached the sample pbix that I'm using. I used your formula and did an audit from mid-May and went back 30 days and the measure was remarkably high compared to what it should've been.
Also - is it weird that my date hierarchy is skipping days? I know they're skipping the weekends, but it's odd that I'm not seeing just $0 revenue on those days, no?
@seanpratt , Add workday column in you Calendar table and try
New column
Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Measure
30DaysThisYear =
CALCULATE(
AVERAGEX(ORDERS,SUM(ORDERS[DIRECT REVENUE])),
DATESINPERIOD('Calendar'[Date], LASTDATE('Calendar'[Date]), -30, DAY), filter('Calendar','Calendar'[work day]=1))
Hmm I think that got us farther away from the solution. Now it's just repeating the daily revenue total. It's taken out the week end days as you can see from "Day" but the 30DaysThisYear formula is now no longer finding the average revenue from 30 weekdays ago to today