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.
@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])
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.
@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])
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |