Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
This is regarding Moving average measure. we have a date column called short date and values in short date will always be less than or equal to today. But the graph is going beyond today. May be by default it is considering whole calendar year.
Formula we used is
FTE rolling average =
IF(
ISFILTERED('redmine hyper_automation_program_view'[ShortDate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = LASTDATE('redmine hyper_automation_program_view'[ShortDate].[Date])
RETURN
AVERAGEX(
DATESBETWEEN(
'redmine hyper_automation_program_view'[ShortDate].[Date],
DATEADD(__LAST_DATE, -50, DAY),
__LAST_DATE
),
CALCULATE(SUM('redmine hyper_automation_program_view'[FTE]))
)
The output is:
Area marked in red should not be appaered in the visual.
Thanks in advance!!
Solved! Go to Solution.
Hi @Anonymous ,
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.If question still not solved ,could you pls share your sample data,and remember to remove confidential data.
Best Regards
Lucien
Hi @Anonymous ,
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.If question still not solved ,could you pls share your sample data,and remember to remove confidential data.
Best Regards
Lucien
Hi @Anonymous
Try this:
FTE rolling average =
VAR __LAST_DATE =
LASTDATE ( 'redmine hyper_automation_program_view'[ShortDate].[Date] )
RETURN
IF (
ISFILTERED ( 'redmine hyper_automation_program_view'[ShortDate] ),
ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
AVERAGEX (
FILTER (
ALL ( 'redmine hyper_automation_program_view' ),
DATESBETWEEN (
'redmine hyper_automation_program_view'[ShortDate].[Date],
DATEADD ( __LAST_DATE, -50, DAY ),
__LAST_DATE
)
),
[FTE]
)
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |