Rolling average populated in the future

[Anonymous]

Hello,

Objective: 

- calculate the last 3 months average -> OK

- populate this number in the future dates -> Challenge 

I have this formulate giving me my average

Last 3 Months Sales = CALCULATE([Sales UoM],FILTER(Dim_Calendar,Dim_Calendar[DAY_DAT]<date(year(today()),month(today()),1)&&Dim_Calendar[DAY_DAT]>=date(year(today()-90),month(today()-90),1))) / 3

But when I plot it under my graph, I have no data in the future. 

And I would like to have it to compare versus my future forecast. 

I would like to achieve this : 

Thanks for your help

Kind regards

[nsexton12]

I am going to attempt to answer this question but will have to make a few assumptions. I am assuming that you want a three month rolling average based on the data in the Sales UoM column for all of those future months.

I think you will need to include an IF statement around your original function. It is populating as blank because your condition with the dates being 3 months from today doesn't work for the future months. You neeed a separate calculate function for months that fall outside of that range. 

So, I would do something like: IF ( <Your Original Function> = BLANK() , <Formula for calculating 3 month average without using today()>, <your original function>)

If you would provide some Excel data, it would be easier to help. However, this is the best I can do with the information provided. 

[Anonymous]

So you are correct nsexton12. 

The challenge for me is about this 3month average function without "today".

Do you have any advice or any hint about this ?

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hello Greg_deckler

PLease see an excel sample of the data 

First column is a measure which is currently ok.
However I am struggling to generate the measure on the right which I just want to be the average of the last 3 months sales (no matter future date)