Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm trying to calculate inventory usage dates based on the demand and expiry.
This allows us to show what inventory will go to waste based on current demand (allowing time to push sales deals or warn finance.)
This works fine in Excel, but I'm struggling to move it to Power Query because I need to reference the cell above. The usual index-merge technique will not work here as the row needs to be calculated based on the calculation of the previous row.
If I were to write this in VBA i would loop through each row, holding the previous row 'stockcompletiondate' in a variable.
Is this functionality available in Power Query? Is there another workaround I should explore?
Excel mock-up below; everything links back to 'stockcompletiondate'
(data changed between screenshots to show more examples)
Edit:
Table of sample data used in the above example.
Site | Product | QTY | SLED | SameSiteAndSku |
1 | A | 100 | 12/05/2024 | FALSE |
1 | A | 53 | 17/05/2024 | TRUE |
1 | A | 80 | 21/05/2024 | TRUE |
1 | b | 1000 | 25/05/2024 | FALSE |
1 | b | 40 | 22/06/2024 | TRUE |
Use List.Accumulate.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Sample data added.
List.Accumulate looks very promising.
I'm unsure how I would work with the multiple columns; I thought that lists are single column.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |