Custom columns problem

[vehau1]

an hour ago

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!

[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!

[dax]

Hi @vehau1 , 

You could try below measure to see whether it work or not.

DateDateWvals-RunningTOtal-RunningPlannedTotal--Prognosis-

Wednesday, January 1, 2020	Wednesday, January 1, 2020	2100	2200
Thursday, January 2, 2020	Thursday, January 2, 2020	4000	4400
Friday, January 3, 2020			6600
Saturday, January 4, 2020			8800

Measure 2 = var  maxd=CALCULATE(max(t1[DateWvals-]),ALLSELECTED(t1))  return if(MIN(t1[DateWvals-]) =BLANK(),SUM(t1[RunningPlannedTotal-])+CALCULATE(SUM(t1[RunningTOtal-])-SUM(t1[RunningPlannedTotal-]), filter (ALLSELECTED(t1),t1[DateWvals-]=maxd)), BLANK())

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@vehau1 , to me it seems like current-day value - last day value one column _ last day value on another column

In the measure, you can get last day value like

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

As a new column, you can try like

RunningTOtalast day = maxx(filter(Table,[Date] =earlier([Date] )-1),[RunningTOtal])