Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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))
Proud to be a Super User!
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))
Proud to be a Super User!
Thank you Ryan! Much appreciated. I based my solution off yours.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |