Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello All,
I'm having some trouble getting a rolling 3 month average to work properly. I've tried 5 different formulas and they return either 0 or blank. I have a Date table with the first date as 01/01/1989 and an ending date of 12/31/2027. My data table has EndOfMonth dates, that start on 01/31/2019 and end with 08/31/2022. I summed the data to a monthly total in SQL, to save time from loading all the rows.
Here is an example of the last formula I tried to use:
Any help would be greatly appreciated.
Solved! Go to Solution.
Have you tried this pattern?
https://www.daxpatterns.com/standard-time-related-calculations/#code61
Sales AVG 3M :=
VAR Period3M =
CALCULATETABLE (
DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date] ),
-3,
MONTH
),
'Date'[DateWithSales] = TRUE
)
VAR FirstDayWithData =
CALCULATE (
MIN ( Sales[Order Date] ),
REMOVEFILTERS ()
)
VAR FirstDayInPeriod =
MINX ( Period3M, 'Date'[Date] )
VAR Result =
IF (
FirstDayWithData <= FirstDayInPeriod,
AVERAGEX (
Period3M,
[Sales Amount]
)
)
RETURN
Result
Proud to be a Super User!
Have you tried this pattern?
https://www.daxpatterns.com/standard-time-related-calculations/#code61
Sales AVG 3M :=
VAR Period3M =
CALCULATETABLE (
DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date] ),
-3,
MONTH
),
'Date'[DateWithSales] = TRUE
)
VAR FirstDayWithData =
CALCULATE (
MIN ( Sales[Order Date] ),
REMOVEFILTERS ()
)
VAR FirstDayInPeriod =
MINX ( Period3M, 'Date'[Date] )
VAR Result =
IF (
FirstDayWithData <= FirstDayInPeriod,
AVERAGEX (
Period3M,
[Sales Amount]
)
)
RETURN
Result
Proud to be a Super User!
User | Count |
---|---|
76 | |
74 | |
61 | |
61 | |
45 |
User | Count |
---|---|
108 | |
103 | |
93 | |
83 | |
64 |