Custom Colums problem: prognosis

[vehau1]

I have a fairly easy problem, but I dont get it right. I want to calculate "Prognosis"

Date	-DateWvals-	-RunningTOtal-	-RunningPlannedTotal-	-Prognosis-
1.1.2020	1.1.2020	2100	2200	blank
1.2.2020	1.2.2020	4000	4400	blank
1.3.2020	blank	blank	6600	X
1.4.2020	blank	blank	8800	X2

Here, X should equal to 6200. 

X = RunningPlannedTotal + (RunningTotal-RunningPlannedTotal, at the last non-blank value of DateWvals)

X = 6600 + (4000-4400) = 6200. And so the X2 would yield: 8800 + (4000-4400) = 8400.

Earlier vals of Prognosis should be blank.

Please help!

[ryan_mayu · Accepted solution]

@vehau1 

you can try below DAX coding to create a column.

Column = 
VAR lastdate1= MAXX(FILTER(Sheet1,'Sheet1'[Date]<EARLIER(Sheet1[Date])&&Sheet1[-RunningTOtal-]<>"blank"),'Sheet1'[Date].[Date])
VAR lasttotal=MAXX(FILTER(sheet1,Sheet1[Date]=lastdate1),Sheet1[-RunningTOtal-])
VAR lastplantotal=MAXX(FILTER(sheet1,Sheet1[Date]=lastdate1),Sheet1[-RunningPlannedTotal-])
return 
if(lastdate1 =0,blank(),Sheet1[-RunningPlannedTotal-]+(lasttotal-lastplantotal))

[vehau1]

Thank you Ryan! Much appreciated. I based my solution off yours.