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.
Hi all,
I am trying to create a rolling averages, in some cases its working but for some it isn't. Let me explain
The base dax for count is as below:
Count =
Var A =
CALCULATE (DISTINCTCOUNT (Table[ClaimNo.]),
Status IN {"OF", "RO"},
Table[OpenDate]<= MAX(CALENDAR[Date])
Var B =
CALCULATE (DISTINCTCOUNT (Table[ClaimNo.]),
Status IN {"ON"},
Table[Closedate]>= MAX(CALENDAR[Date])
Return
A+B
As above the count dax , I have created total paid dax as well
Now when I try to use these in another dax for rolling average it is not counting average of rest 5 months but is calculating for a single month
Following is the dax I am using for rolling 6month average
6M AVG =
Var A = CALCULATE ( [Count],
DATESINPERIOD(CALENDAR[Date],
MAX ( CALENDAR[Date]),
-6,
Month))
Var B = CALCULATE ( [Total Paid],
DATESINPERIOD(CALENDAR[Date],
MAX ( CALENDAR[Date]),
-6,
Month))
Return
DIVIDE (B, A, 0)
This is just giving me month wise calculation and not rolling 6 months value
Can you please suggest what can be done for this?
There is no active relationship between Table and CALENDAR
Thanks in advance
Solved! Go to Solution.
Hey @Sirona_Kis
is there any reason why there is no active relationship between Table and Calendar?
Could be a bit challanging to make a time intelligence calculation.
Have you tried to use AVERAGEX() to get your rolling avg?
Like this:
Formula used:
Rolling Avg =
VAR Period =
DATESINPERIOD(
'Calendar'[Date],
MAX('Calendar'[Date]),
-6,
MONTH)
RETURN
CALCULATE(
AVERAGEX(
VALUES('Calendar'[Year Month Number]),
[Qty]),
Period)
Regards
Hey @Sirona_Kis
is there any reason why there is no active relationship between Table and Calendar?
Could be a bit challanging to make a time intelligence calculation.
Have you tried to use AVERAGEX() to get your rolling avg?
Like this:
Formula used:
Rolling Avg =
VAR Period =
DATESINPERIOD(
'Calendar'[Date],
MAX('Calendar'[Date]),
-6,
MONTH)
RETURN
CALCULATE(
AVERAGEX(
VALUES('Calendar'[Year Month Number]),
[Qty]),
Period)
Regards
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |