50 days Moving Average graph exceeding date values in the column

[Anonymous]

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

[v-luwang-msft · Accepted 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

[VahidDM]

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/