Forum Discussion
Issue With Rolling 3 Month Average
Community ChampionAlright. Let's try it this way
Net Revenue R3M :=
VAR NumOfMonths = 3
VAR LastSelectedDate =
MAX ( D_DATE[Calendar_Date] )
VAR Period =
DATESINPERIOD ( D_DATE[Calendar_Date], LastSelectedDate, - NumOfMonths, MONTH )
VAR Result =
AVERAGEX (
SUMMARIZE (
CALCULATETABLE ( D_DATE, Period ),
D_DATE[Month Year],
D_DATE[Year]
),
[Total Net Revenue by DOS]
)
RETURN
Result
I gave this a shot this morning and still had the same issue. I created a new, pared down version with just the fact and date tables (posted in reply to another comment), no dimensions. Seem to be getting the same type of issue even with just two tables and one join.
It looks like that was the issue. I didn't realize the date table had to be specifically marked inside my SSIS Tabular model for those types of calculations to work. I apologize for all the time spent on something so simple - part of being new to DAX/Power BI I guess - but I appreciate all of the help! Thank you so much!
- tamerj1
No problem. Actually it was late at night yesterday when I was trying to find a solution and directly after that went to sleep. The first thing that came to my mind before I close my eyes was the date table. Actually time intelligence functions include many hidden functions that do not work if the table was not marked as date table. DATESINPERIOD includes REMOVEFILTERS which was apparently not working.