Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous ,

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

Anonymous
Not applicable

If statement works fine.

View solution in original post

4 REPLIES 4
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

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.
Anonymous
Not applicable

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

Hi @Anonymous ,

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.
Anonymous
Not applicable

If statement works fine.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.