Forum Discussion
Continue last result in calculation
Hi there,
I feel like I'm 80% there with this solution but just need a little bit more help.
I'm looking to project my forward inventory, based on the last available data, into the future. The Inventory value in the future would be:
"Projected SOH = Last weeks Network SOH - Adjusted FC + On Order"
In the below screenshot I've managed to get last weeks inventory from week 46 into week 47, and applied the formula to it WHICH WORKS!
My issue now is using that result in the forward calculation, I'm fairly new to DAX so I don't know if there is a key step or element I'm missing. I'm also using an IF funtion to land on the last week number so I'm not sure if there's a cleaner way to do that.
Is there a way to use the last result from my measure into future calculations of the same measure? I've tried re-doing it with a running total but can't seem to get anywhere.
Last Weeks OH =
if([VALUE for Network SOH]=0,
calculate(
SUM('Forecast Data'[VALUE]),
'Forecast Data'[MEASURE] IN { "Network SOH" },
filter( ALL ( 'Date' ),
'Date'[Year] = MAX ( 'Date'[Year] )
&& 'Date'[weekIndex]
= MAX ( 'Date'[weekIndex] ) - 1))+'Forecast Data'[VALUE for On Order]-'FC Adj %'[Adjusted FC],
0)
My current main data set is below. I've created a date calendar with an index column to find last weeks data.
Please any help anyone could give would be appreciated I've spent some late nights lately trying to rework things found in other posts.
Appreciate your help in advance
Hi fatconductor ,
Please have a try.
Create a measure.
Measure_1 = CALCULATE ( SUM ( 'Test Data'[VALUE] ), FILTER ( ALL ( 'Test Data' ), 'Test Data'[VERSION] = "Actual/Forecast" && 'Test Data'[FIN_WEEK] = SELECTEDVALUE ( 'Test Data'[FIN_WEEK] ) && 'Test Data'[MEASURE] = SELECTEDVALUE ( 'Test Data'[MEASURE] ) ) )Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- lbendlin
Super User
Power BI has no memory, and no global variables. You need to solve this issue by using an aggregator function, specifically SUMX() that computes your inventory movements from a base date to the current filter context. For every data point!
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hey mate,
Thanks so much I'll try and work SUMX into the formula and see if that works. In the meantime sample data is below with an expected outcome screenshot.
VERSION ITEM_NO LOCATION FIN_WEEK MEASURE VALUE Actual/Forecast 109382 2560 2022 Week 30 Network SOH 2299 Actual/Forecast 109382 5520 2022 Week 30 Network SOH 1273 Actual/Forecast 109382 6560 2022 Week 30 Network SOH 2264 Actual/Forecast 109382 7350 2022 Week 30 Network SOH 6527 Actual/Forecast 109382 8350 2022 Week 30 Network SOH 2499 Actual/Forecast 109382 2560 2022 Week 31 Network SOH 2899 Actual/Forecast 109382 5520 2022 Week 31 Network SOH 1228 Actual/Forecast 109382 6560 2022 Week 31 Network SOH 1819 Actual/Forecast 109382 7350 2022 Week 31 Network SOH 10616 Actual/Forecast 109382 8350 2022 Week 31 Network SOH 3240 Actual/Forecast 109382 2560 2022 Week 32 Network SOH 2425 Actual/Forecast 109382 5520 2022 Week 32 Network SOH 912 Actual/Forecast 109382 6560 2022 Week 32 Network SOH 1979 Actual/Forecast 109382 7350 2022 Week 32 Network SOH 9285 Actual/Forecast 109382 8350 2022 Week 32 Network SOH 2783 Actual/Forecast 109382 2560 2022 Week 33 Network SOH 2657 Actual/Forecast 109382 5520 2022 Week 33 Network SOH 767 Actual/Forecast 109382 6560 2022 Week 33 Network SOH 1621 Actual/Forecast 109382 7350 2022 Week 33 Network SOH 7387 Actual/Forecast 109382 8350 2022 Week 33 Network SOH 2339 Actual/Forecast 109382 2560 2022 Week 34 Network SOH 3446 Actual/Forecast 109382 5520 2022 Week 34 Network SOH 791 Actual/Forecast 109382 6560 2022 Week 34 Network SOH 3297 Actual/Forecast 109382 7350 2022 Week 34 Network SOH 6030 Actual/Forecast 109382 8350 2022 Week 34 Network SOH 2353 Actual/Forecast 109382 6560 2022 Week 30 On Order 4608 Actual/Forecast 109382 7350 2022 Week 30 On Order 768 Actual/Forecast 109382 6560 2022 Week 31 On Order 3840 Actual/Forecast 109382 8350 2022 Week 31 On Order 4608 Actual/Forecast 109382 7350 2022 Week 33 On Order 3072 Actual/Forecast 109382 8350 2022 Week 35 On Order 3840 Actual/Forecast 109382 7350 2022 Week 36 On Order 5376 Actual/Forecast 109382 8350 2022 Week 36 On Order 2304 Actual/Forecast 109382 6560 2022 Week 37 On Order 768 Actual/Forecast 109382 8350 2022 Week 37 On Order 3072 Actual/Forecast 109382 6560 2022 Week 38 On Order 3840 Actual/Forecast 109382 7350 2022 Week 38 On Order 7680 Actual/Forecast 109382 6560 2022 Week 39 On Order 10752 Actual/Forecast 109382 7350 2022 Week 39 On Order 8448 Actual/Forecast 109382 8350 2022 Week 39 On Order 4608 Actual/Forecast 109382 2560 2022 Week 30 Units Sold 302 Actual/Forecast 109382 5520 2022 Week 30 Units Sold 289 Actual/Forecast 109382 6560 2022 Week 30 Units Sold 634 Actual/Forecast 109382 7350 2022 Week 30 Units Sold 1532 Actual/Forecast 109382 8350 2022 Week 30 Units Sold 735 Actual/Forecast 109382 2560 2022 Week 31 Units Sold 168 Actual/Forecast 109382 5520 2022 Week 31 Units Sold 276 Actual/Forecast 109382 6560 2022 Week 31 Units Sold 603 Actual/Forecast 109382 7350 2022 Week 31 Units Sold 1171 Actual/Forecast 109382 8350 2022 Week 31 Units Sold 454 Actual/Forecast 109382 2560 2022 Week 32 Units Sold 380 Actual/Forecast 109382 5520 2022 Week 32 Units Sold 406 Actual/Forecast 109382 6560 2022 Week 32 Units Sold 570 Actual/Forecast 109382 7350 2022 Week 32 Units Sold 1634 Actual/Forecast 109382 8350 2022 Week 32 Units Sold 767 Actual/Forecast 109382 2560 2022 Week 33 Units Sold 532 Actual/Forecast 109382 5520 2022 Week 33 Units Sold 177 Actual/Forecast 109382 6560 2022 Week 33 Units Sold 491 Actual/Forecast 109382 7350 2022 Week 33 Units Sold 1983 Actual/Forecast 109382 8350 2022 Week 33 Units Sold 663 Actual/Forecast 109382 2560 2022 Week 34 Units Sold 460 Actual/Forecast 109382 5520 2022 Week 34 Units Sold 214 Actual/Forecast 109382 6560 2022 Week 34 Units Sold 527 Actual/Forecast 109382 7350 2022 Week 34 Units Sold 1602 Actual/Forecast 109382 8350 2022 Week 34 Units Sold 461 Actual/Forecast 109382 2560 2022 Week 35 Units Sold 434 Actual/Forecast 109382 6560 2022 Week 35 Units Sold 1593 Actual/Forecast 109382 7350 2022 Week 35 Units Sold 1786 Actual/Forecast 109382 8350 2022 Week 35 Units Sold 1013 Actual/Forecast 109382 2560 2022 Week 36 Units Sold 437 Actual/Forecast 109382 6560 2022 Week 36 Units Sold 1604 Actual/Forecast 109382 7350 2022 Week 36 Units Sold 1798 Actual/Forecast 109382 8350 2022 Week 36 Units Sold 1021 Actual/Forecast 109382 2560 2022 Week 37 Units Sold 420 Actual/Forecast 109382 6560 2022 Week 37 Units Sold 1540 Actual/Forecast 109382 7350 2022 Week 37 Units Sold 1727 Actual/Forecast 109382 8350 2022 Week 37 Units Sold 980 Actual/Forecast 109382 2560 2022 Week 38 Units Sold 422 Actual/Forecast 109382 6560 2022 Week 38 Units Sold 1546 Actual/Forecast 109382 7350 2022 Week 38 Units Sold 1734 Actual/Forecast 109382 8350 2022 Week 38 Units Sold 984 Actual/Forecast 109382 2560 2022 Week 39 Units Sold 391 Actual/Forecast 109382 6560 2022 Week 39 Units Sold 1434 Actual/Forecast 109382 7350 2022 Week 39 Units Sold 1608 Actual/Forecast 109382 8350 2022 Week 39 Units Sold 913
Really appreciate the help here thankhou.Hey there,
Thanks so much for the help Ibendin. Please see below sample data.
VERSION ITEM_NO LOCATION FIN_WEEK MEASURE VALUE Actual/Forecast 109382 2560 2022 Week 30 Network SOH 2299 Actual/Forecast 109382 5520 2022 Week 30 Network SOH 1273 Actual/Forecast 109382 6560 2022 Week 30 Network SOH 2264 Actual/Forecast 109382 7350 2022 Week 30 Network SOH 6527 Actual/Forecast 109382 8350 2022 Week 30 Network SOH 2499 Actual/Forecast 109382 2560 2022 Week 31 Network SOH 2899 Actual/Forecast 109382 5520 2022 Week 31 Network SOH 1228 Actual/Forecast 109382 6560 2022 Week 31 Network SOH 1819 Actual/Forecast 109382 7350 2022 Week 31 Network SOH 10616 Actual/Forecast 109382 8350 2022 Week 31 Network SOH 3240 Actual/Forecast 109382 2560 2022 Week 32 Network SOH 2425 Actual/Forecast 109382 5520 2022 Week 32 Network SOH 912 Actual/Forecast 109382 6560 2022 Week 32 Network SOH 1979 Actual/Forecast 109382 7350 2022 Week 32 Network SOH 9285 Actual/Forecast 109382 8350 2022 Week 32 Network SOH 2783 Actual/Forecast 109382 2560 2022 Week 33 Network SOH 2657 Actual/Forecast 109382 5520 2022 Week 33 Network SOH 767 Actual/Forecast 109382 6560 2022 Week 33 Network SOH 1621 Actual/Forecast 109382 7350 2022 Week 33 Network SOH 7387 Actual/Forecast 109382 8350 2022 Week 33 Network SOH 2339 Actual/Forecast 109382 2560 2022 Week 34 Network SOH 3446 Actual/Forecast 109382 5520 2022 Week 34 Network SOH 791 Actual/Forecast 109382 6560 2022 Week 34 Network SOH 3297 Actual/Forecast 109382 7350 2022 Week 34 Network SOH 6030 Actual/Forecast 109382 8350 2022 Week 34 Network SOH 2353 Actual/Forecast 109382 6560 2022 Week 30 On Order 4608 Actual/Forecast 109382 7350 2022 Week 30 On Order 768 Actual/Forecast 109382 6560 2022 Week 31 On Order 3840 Actual/Forecast 109382 8350 2022 Week 31 On Order 4608 Actual/Forecast 109382 7350 2022 Week 33 On Order 3072 Actual/Forecast 109382 8350 2022 Week 35 On Order 3840 Actual/Forecast 109382 7350 2022 Week 36 On Order 5376 Actual/Forecast 109382 8350 2022 Week 36 On Order 2304 Actual/Forecast 109382 6560 2022 Week 37 On Order 768 Actual/Forecast 109382 8350 2022 Week 37 On Order 3072 Actual/Forecast 109382 6560 2022 Week 38 On Order 3840 Actual/Forecast 109382 7350 2022 Week 38 On Order 7680 Actual/Forecast 109382 6560 2022 Week 39 On Order 10752 Actual/Forecast 109382 7350 2022 Week 39 On Order 8448 Actual/Forecast 109382 8350 2022 Week 39 On Order 4608 Actual/Forecast 109382 2560 2022 Week 30 Units Sold 302 Actual/Forecast 109382 5520 2022 Week 30 Units Sold 289 Actual/Forecast 109382 6560 2022 Week 30 Units Sold 634 Actual/Forecast 109382 7350 2022 Week 30 Units Sold 1532 Actual/Forecast 109382 8350 2022 Week 30 Units Sold 735 Actual/Forecast 109382 2560 2022 Week 31 Units Sold 168 Actual/Forecast 109382 5520 2022 Week 31 Units Sold 276 Actual/Forecast 109382 6560 2022 Week 31 Units Sold 603 Actual/Forecast 109382 7350 2022 Week 31 Units Sold 1171 Actual/Forecast 109382 8350 2022 Week 31 Units Sold 454 Actual/Forecast 109382 2560 2022 Week 32 Units Sold 380 Actual/Forecast 109382 5520 2022 Week 32 Units Sold 406 Actual/Forecast 109382 6560 2022 Week 32 Units Sold 570 Actual/Forecast 109382 7350 2022 Week 32 Units Sold 1634 Actual/Forecast 109382 8350 2022 Week 32 Units Sold 767 Actual/Forecast 109382 2560 2022 Week 33 Units Sold 532 Actual/Forecast 109382 5520 2022 Week 33 Units Sold 177 Actual/Forecast 109382 6560 2022 Week 33 Units Sold 491 Actual/Forecast 109382 7350 2022 Week 33 Units Sold 1983 Actual/Forecast 109382 8350 2022 Week 33 Units Sold 663 Actual/Forecast 109382 2560 2022 Week 34 Units Sold 460 Actual/Forecast 109382 5520 2022 Week 34 Units Sold 214 Actual/Forecast 109382 6560 2022 Week 34 Units Sold 527 Actual/Forecast 109382 7350 2022 Week 34 Units Sold 1602 Actual/Forecast 109382 8350 2022 Week 34 Units Sold 461 Actual/Forecast 109382 2560 2022 Week 35 Units Sold 434 Actual/Forecast 109382 6560 2022 Week 35 Units Sold 1593 Actual/Forecast 109382 7350 2022 Week 35 Units Sold 1786 Actual/Forecast 109382 8350 2022 Week 35 Units Sold 1013 Actual/Forecast 109382 2560 2022 Week 36 Units Sold 437 Actual/Forecast 109382 6560 2022 Week 36 Units Sold 1604 Actual/Forecast 109382 7350 2022 Week 36 Units Sold 1798 Actual/Forecast 109382 8350 2022 Week 36 Units Sold 1021 Actual/Forecast 109382 2560 2022 Week 37 Units Sold 420 Actual/Forecast 109382 6560 2022 Week 37 Units Sold 1540 Actual/Forecast 109382 7350 2022 Week 37 Units Sold 1727 Actual/Forecast 109382 8350 2022 Week 37 Units Sold 980 Actual/Forecast 109382 2560 2022 Week 38 Units Sold 422 Actual/Forecast 109382 6560 2022 Week 38 Units Sold 1546 Actual/Forecast 109382 7350 2022 Week 38 Units Sold 1734 Actual/Forecast 109382 8350 2022 Week 38 Units Sold 984 Actual/Forecast 109382 2560 2022 Week 39 Units Sold 391 Actual/Forecast 109382 6560 2022 Week 39 Units Sold 1434 Actual/Forecast 109382 7350 2022 Week 39 Units Sold 1608 Actual/Forecast 109382 8350 2022 Week 39 Units Sold 913Hi Ibendin,
Please see below link for sample data.
https://drive.google.com/file/d/1PK0ot2B9x5Wix3YqtRgGVjo_DYMlcZsb/view?usp=sharing
And below for expected outcome. Appreciate the help.- lbendlin
Thank you for providing the sample data. That helps a lot with proposing a potential solution.
"Projected SOH = Last weeks Network SOH - Adjusted FC + On Order"
Which part of your sample data is the Adjusted Forecast?
Hi fatconductor ,
Please have a try.
Create a measure.
Measure_1 = CALCULATE ( SUM ( 'Test Data'[VALUE] ), FILTER ( ALL ( 'Test Data' ), 'Test Data'[VERSION] = "Actual/Forecast" && 'Test Data'[FIN_WEEK] = SELECTEDVALUE ( 'Test Data'[FIN_WEEK] ) && 'Test Data'[MEASURE] = SELECTEDVALUE ( 'Test Data'[MEASURE] ) ) )Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
