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.
Hi all,
I'm looking for some help with the following inventory scenario:
Current Result:
Desired result:
The On Hand value (shown in blue) should only be used to determine the Ending Stock value for the first row.
All other rows should use the Ending Stock value (shown in red) from the previous row as the starting point for the week's Ending Stock value (shown in red).
Any help would be greatly appreciated!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Share the raw data (in a format that can be pasted in an MS Excel file) from where you want to generate the second table.
Thanks @Ashish_Mathur - here's a table showing the format of the raw data in a simplified format
(I'm having trouble posting a table here, so I've separated the fields using pipe symbol):
Week Ending | 5/07/21 | 5/14/21 | 5/21/21 | 5/28/21 | 6/04/21 | 6/11/21
Quantity on Hand | 1230.8 | 1230.8 | 1346.8 | 1346.8 | 1778.8 | 1778.8
Quantity In Production | 0 | 288 | 0 | 432 | 0 | 0
Quantity on Sales Orders | 255 | 255 | 255 | 255 | 255 | 255
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks so much Ashish! This worked out perfectly.
Quick follow-up question - I've used a Calendar Table developed in Power Query, that is not based on the min date of the dataset, rather the start of the year. Would there be a way to make the formulas work with that Calendar Table?
Hi,
You are welcome. I will have to tweak my formulas for that. For now, please adjust your Calendar table to start from the first date of the dataset.
This should be calculated in Power Query. I hope you're not trying to do it in DAX...
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 |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |