Get the latest value based on date slicer and then calculate that for forecast

[userdata]

Hi,

so there are the actuals the ytd actuals based on the date that the user selects. Then the forecast is what for the months that are in the future. I want to be able to create a column in power bi with the red line (difference of new column based on previous column.

I want to be able to only show the last ytd value for the date that is selected. In this case it is 95 and use that as a starting point to do that 95 - what is in forecast and then the output of that - next column. So meaning , I want to calculate 95 which is last ytd where there is data and then do that minus 12 what is in forecast which gives 83, and then do that output 83 -14 (which is the forecast the next month) which gives 69 and that minus the following month data in forecast etc. I tried this several times in Dax but it didn't work. 

Any ideas how to get the red column?

[userdata]

Hi @amitchandak ,

thanks so much for your quick help! You are a hero!

There is just one thing I have to do here, your measure does give ytd of all the previous months and the right variance but how do I get rid of the previous months and only show from the yellow, basically just show the latest value of ytd actuals and don't show the previous months ytd in the measure? Thanks so much!

[amitchandak]

@userdata , if actual and forecast are in different table , using date table

Measure =
var _max =maxx(allselected(Table), Table[Date])
return
if(Date[Date] >=_max , calculate(sum(Table[Actual]) - sum(Forecast[Forecast]), datesytd(Date[Date])))

In same table 

Measure =
var _max =maxx(filter(allselected(Table), not(isblank(Table[Actual]))) , Table[Date])
return
if(Date[Date] >=_max , calculate(sum(Table[Actual]) - sum(Table[Forecast]), datesytd(Date[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.