Rolling 12 month average to populate going forward

[Bryanna]

Hi,

I have a use case where I am trying to show a rolling 12 month average but populate the missing data for dates where nothing occured. I figured out how to do the rolling 12 month but it doesnt continue foward.  Any thoughts?

See measure below:

Measure 2 =

    VAR NumOfMonths = 12

VAR LastCurrentDate =

    MAX ( 'Calendar Table'[Month End] )

VAR Period =

    DATESINPERIOD ( 'Calendar Table'[Month End], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

    CALCULATE (

        AVERAGEX (

            VALUES ( 'Tracker'[Month End Date] ),

            [sum VMS$]

        ),

        Period

    )

VAR FirstDateInPeriod = MINX ( Period, 'Calendar Table'[Month End] )

VAR LastDateWithSales = MAX ( 'Calendar Table'[Month End] )

RETURN

    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

[Bryanna]

Hi,

Its not letting me add the pbi file. The expected output is below where if this were to continue to forward instead of the value being zero it would populate a 12 month average , if there isnt a 12 month average to calculate then it could just pull the last value that was available. I also need this to sum then correctly at the bottom. 

Current output it is showing anytime there is a blank field it will populate that last value even prior to when the project started. And also is not summing correctly.

[Anonymous]

Hi @Bryanna ,

Make sure your Calendar Table contains future dates outside of the current data range.

Modify measure to calculate the average for all periods, including periods projected into the future, not the last date of sales.

Measure 2 =
VAR NumOfMonths = 12
VAR LastCurrentDate = MAX ( 'Calendar Table'[Month End] )
VAR Period = DATESINPERIOD ( 'Calendar Table'[Month End], LastCurrentDate, -NumOfMonths, MONTH )
VAR Result = CALCULATE (
    AVERAGEX (
        VALUES ( 'Tracker'[Month End Date] ),
        [sum VMS$]
    ),
    Period
)
RETURN IF ( ISBLANK( Result ), BLANK(), Result )

You can also refer to the solution of this case, which may help you with your problem.

Solved: Calculate Rolling 12 Month Average Headcount - Microsoft Fabric Community

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.