topic 50 days Moving Average graph exceeding date values in the column in DAX Commands and Tips

50 days Moving Average graph exceeding date values in the column

Anonymous — Fri, 26 Nov 2021 06:06:59 GMT

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!!

Re: 50 days Moving Average graph exceeding date values in the column

VahidDM — Fri, 26 Nov 2021 06:27:07 GMT

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/

Re: 50 days Moving Average graph exceeding date values in the column

v-luwang-msft — Thu, 02 Dec 2021 07:30:05 GMT

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