cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Drake23 Regular Visitor
Regular Visitor

Future graph shown in 12 months moving average

Hi Guys,

 

I have created a 12 months moving average by using quick measure but my line is showing future months data also.

For example I have date till March 31st but the 12 months graph is showing till Year end.

 

Regards

Drake

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Future graph shown in 12 months moving average

Hi @Drake23 ,

You could modify the quick measure like below.

value rolling average 2 =
IF (
    ISFILTERED ( 'Sheet25'[Date] ),
    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 =
        ENDOFMONTH ( 'Sheet25'[Date].[Date] )
    VAR __DATE_PERIOD =
        DATESBETWEEN (
            'Sheet25'[Date].[Date],
            STARTOFMONTH ( DATEADD ( __LAST_DATE, -12, MONTH ) ),
            ENDOFMONTH ( DATEADD ( __LAST_DATE, 1, MONTH ) )
        )
    VAR average_ =
        AVERAGEX (
            CALCULATETABLE (
                SUMMARIZE (
                    VALUES ( 'Sheet25' ),
                    'Sheet25'[Date].[Year],
                    'Sheet25'[Date].[QuarterNo],
                    'Sheet25'[Date].[Quarter],
                    'Sheet25'[Date].[MonthNo],
                    'Sheet25'[Date].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE ( SUM ( 'Sheet25'[value] ), ALL ( 'Sheet25'[Date].[Day] ) )
        )
    RETURN
        IF ( MAX ( 'Sheet25'[Date] ) = BLANK (), BLANK (), average_ )
)

Then you could get your desired output.

Capture.PNG

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Drake23 Regular Visitor
Regular Visitor

Re: Future graph shown in 12 months moving average

If statement works fine.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Future graph shown in 12 months moving average

Hi @Drake23 ,

By my test, I could reproduce your issue when I create quick measure like below.

quick measure.PNG

 

This issue is casued by that we set the Periods before to be 12 so it will calculate till year end.

For your requirement, you could set the parameter of Periods before to be 0 so that it will calculate the rolling average till the last date in your data sample.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Drake23 Regular Visitor
Regular Visitor

Re: Future graph shown in 12 months moving average

Hi

I dont think we can keep 0 there, I need to calculate 12 months rolling avg and If i keep o then measure is not working.

 

Regards

Drake

Community Support Team
Community Support Team

Re: Future graph shown in 12 months moving average

Hi @Drake23 ,

You could modify the quick measure like below.

value rolling average 2 =
IF (
    ISFILTERED ( 'Sheet25'[Date] ),
    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 =
        ENDOFMONTH ( 'Sheet25'[Date].[Date] )
    VAR __DATE_PERIOD =
        DATESBETWEEN (
            'Sheet25'[Date].[Date],
            STARTOFMONTH ( DATEADD ( __LAST_DATE, -12, MONTH ) ),
            ENDOFMONTH ( DATEADD ( __LAST_DATE, 1, MONTH ) )
        )
    VAR average_ =
        AVERAGEX (
            CALCULATETABLE (
                SUMMARIZE (
                    VALUES ( 'Sheet25' ),
                    'Sheet25'[Date].[Year],
                    'Sheet25'[Date].[QuarterNo],
                    'Sheet25'[Date].[Quarter],
                    'Sheet25'[Date].[MonthNo],
                    'Sheet25'[Date].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE ( SUM ( 'Sheet25'[value] ), ALL ( 'Sheet25'[Date].[Day] ) )
        )
    RETURN
        IF ( MAX ( 'Sheet25'[Date] ) = BLANK (), BLANK (), average_ )
)

Then you could get your desired output.

Capture.PNG

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Drake23 Regular Visitor
Regular Visitor

Re: Future graph shown in 12 months moving average

If statement works fine.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)