Iterating over records to calculate totals

[Dayna]

Hello,

I have a table that provides a record when a value has increased, or decreased. For example:

Date	Product	Type	Value
01/07/2022	ItemX	Supply	100
03/07/2022	ItemX	Demand	200
06/07/2022	ItemX	Supply	150
02/07/2022	ItemY	Supply	250
05/07/2022	ItemY	Supply	100
06/07/2022	ItemY	Demand	175

What I'll do is join the current inventory we have in stock to give me my starting number, for example:

Date	Product	Type	Value	Stock Value
30/06/2022	ItemX	Inventory		200
30/06/2022	ItemY	Inventory		300
01/07/2022	ItemX	Supply	100
03/07/2022	ItemX	Demand	200
06/07/2022	ItemX	Supply	150
02/07/2022	ItemY	Supply	250
05/07/2022	ItemY	Supply	100
06/07/2022	ItemY	Demand	175

What I then need to do, is iterate over the records to get a running total of what we have, but also fill in the days that are blank.

In my example, my records would then look like this:

Date	Product	Type	Value	Stock Value	Daily Total
30/06/2022	ItemX	Inventory		200	200
30/06/2022	ItemY	Inventory		300	300
01/07/2022	ItemX	Supply	100		300
02/07/2022	ItemX				300
03/07/2022	ItemX	Demand	200		100
04/07/2022	ItemX				100
05/07/2022	ItemX				100
06/07/2022	ItemX	Supply	150		250
01/07/2022	ItemY				300
02/07/2022	ItemY	Supply	250		550
03/07/2022	ItemY				550
04/07/2022	ItemY				550
05/07/2022	ItemY	Supply	100		650
06/07/2022	ItemY	Demand	175		475

So what I've done is start with my inventory value, and where I have a supply record, I've added this to the running total, and where I have demand, I've subtracted it. If there's no changes in the day, I've used the previous value.

I did this in QlikView, but I have no idea where to start in PowerBI, whether I prep it in data view (i.e. grouping all my records and joining the inventory), but then how do I iterate over each record?

Any advice you can offer would be very much appreciated.

Many thanks,

Dayna

[Dayna]

Thank you so much for your help and steps, that's amazing - thanks again!

[PaulDBrown · Accepted solution]

I suggest you model your data by creating separate tables for inventory, demand and supply:

from:

OrignalTo this for each type

Create a dimension table for the products and a date table, and create the relationships between the corresponding fields:

 Use the fields from the dimension tables in measures, visuals, filters, slicers...

Next create simple sum measures for each type and this final measure to get the Daily total:

Value Daily total =
VAR RunningInventory =
    CALCULATE (
        [Sum inventory],
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
        )
    )
VAR RunningSupply =
    CALCULATE (
        [Sum Supply],
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
        )
    )
VAR RunningDemand =
    CALCULATE (
        [Sum Demand],
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
        )
    )
RETURN
    RunningInventory + RunningSupply - RunningDemand

To get

I've attached the sample PBIX file