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.
I am trying to take the average of the "Total Cores" for the latest 4 weeks. The table on the left shows the date field as numeric, which is what I used in the calculation for creating a Measure to calculate the average. However, it returns the the most recent Core value and not the average of the last 4 week total core values. Can someone please help me figure out my mistake? Thank you!
Solved! Go to Solution.
Hi @minnielahoti ,
According to your statement, I know you want to calcualte the average in the latest 4 weeks. However, you filter from the max [Date_num] and [Date_num] - 4 (20220617 to 20220613). I think it is incorrect. We only have 20220617 in range, so it returns the the most recent Core value.
Please try my code as below.
4_weeks_Avg =
VAR _4WEEKEND =
MAXX ( ALL ( CoresByDate ), 'CoresByDate'[Date] )
VAR _4WEEKSTART = _4WEEKEND - 4 * 7
RETURN
CALCULATE (
AVERAGE ( CoresByDate[TotalCores] ),
FILTER (
ALL ( CoresByDate ),
CoresByDate[Date] <= _4WEEKEND
&& CoresByDate[Date] > _4WEEKSTART
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @minnielahoti ,
According to your statement, I know you want to calcualte the average in the latest 4 weeks. However, you filter from the max [Date_num] and [Date_num] - 4 (20220617 to 20220613). I think it is incorrect. We only have 20220617 in range, so it returns the the most recent Core value.
Please try my code as below.
4_weeks_Avg =
VAR _4WEEKEND =
MAXX ( ALL ( CoresByDate ), 'CoresByDate'[Date] )
VAR _4WEEKSTART = _4WEEKEND - 4 * 7
RETURN
CALCULATE (
AVERAGE ( CoresByDate[TotalCores] ),
FILTER (
ALL ( CoresByDate ),
CoresByDate[Date] <= _4WEEKEND
&& CoresByDate[Date] > _4WEEKSTART
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |