Full Year FC YTD measure for As-is / Forecast / Budget

[Adrian_2207]

Hi everybody,

I'm working on a small PowerBi Dashboard for tracking the as-is Sales Volumes vs. Forecast and Budget on monthly base.

I would like to have a measure for the Full Year FC but I have no idea to implement this.

Here my example for Feburary:

 

The SalesVolumes for the last month are avaiable after the month end closing activity. During the month there will be no update of the Sales Volumes. For the example above the current month is Feburary (--> That means January is done / closed already) 

The ForecastVolumes are always avaiable for the next 3 month after a closed month.
In my example means that: 

1.  As-is Volumes (month is already closed)
2. ForecastVolumes 
3. ForecastVolumes
4. ForecastVolumes
5. BudgetVolumes
6. BugdetVolumes
7. BugdetVolumes
8. BugdetVolumes
9. BugdetVolumes
10. BugdetVolumes
11. BugdetVolumes
12. BugdetVolumes

I would like to have a measure which finds out what was the last as-is sales month. Based on this + 3 Month Forecast Volumes and than rest of the year are the BudgetVolumes.

Here my datamodell:

I hope that somebody coould support me. I'm a rookie in PowerBi.
Thanks' in advance.

[amitchandak]

Try something like this

Forecast next three =
var _min = maxx(sales,sales_date)
var _max = maxx(sales,dateadd(sales_date,3,MONTH))

return
calculate(sum(forecast[value]),filter(forecast,forecast[date]>=_min  && forecast[date]<=_max)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601