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.
Hello everyone,
I built a power bi report that can show the inventory value now. And it can also show what was received each day and what was processed. So, let's say today's inventory value is $100,000 and we received $10,000 and processed $20,000 today so that would mean our inventory was $110,000 yesterday right!
I want to create a system where it will back track and find the inventry value of each day in the last 10 months and it will also start calculating it from now on in the future. All this will be based off of today's inventory value.
Any help?
Solved! Go to Solution.
Hi @power2 ,
You can follow the steps below to get it, please find the details in the attachment.
1. Add index column in Power Query Editor
= Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
2. Create a calculated column as below
Expected Column =
VAR _inventory =
CALCULATE ( SUM ( 'Table'[Inventory Value] ), ALL ( 'Table' ) )
RETURN
CALCULATE (
_inventory + SUM ( 'Table'[OUT] )
- SUM ( 'Table'[IN] ),
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) - 1 )
)
Best Regards
Here is a visual representation of what I am looking for. Obviolusy, if I did this on excel, I would just drag up and it would fill in the value based on the easy logic that I did (Current Value - In + Out)
I need to implement this in Power Bi with a DAX code.
Hi @power2 ,
You can follow the steps below to get it, please find the details in the attachment.
1. Add index column in Power Query Editor
= Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
2. Create a calculated column as below
Expected Column =
VAR _inventory =
CALCULATE ( SUM ( 'Table'[Inventory Value] ), ALL ( 'Table' ) )
RETURN
CALCULATE (
_inventory + SUM ( 'Table'[OUT] )
- SUM ( 'Table'[IN] ),
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) - 1 )
)
Best Regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
10 | |
3 | |
3 | |
2 | |
2 |