iterative recalculate the inventory value.

[Anonymous]

Hi all,

I have a question about the daily inventory value of my products. 

The inventory value (IV) is based on the following formula:

IV t-1 = IV t + SALES t-1

IV t-2 = IV t-1 + SALES t-2

            .

            .

            .
IV t-182 = IV t-181 + SALES t-182

I could easily acces all of the 182 amount of the SALES days.

How could you easily calculate those 182 inventory values?

IV t = the inventory value today*

IV t-1 = the inventory value yesterday*

I was thinking of creating a empty date table with dates ranging from t-182 to t and then iteratively filling that table, but can't figure out how that would work. 

Thanks in advance!

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

You can create a measure to display the results.

Like this:

Measure =
VAR a =
    CALCULATE ( MAX ( 'Table'[date] ), ALL ( 'Table' ) )
VAR b =
    MAXX ( FILTER ( ALL ( 'Table' ), [date] = a ), [iventory value] )
VAR c =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            [date] >= SELECTEDVALUE ( 'Table'[date] )
                && [date] <> a
        ),
        [daily sales]
    )
RETURN
    b + c

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi all,

Underneath the data. I can't upload an excel file. But here in 3 parts:

date
19/05/2021
20/05/2021
21/05/2021
22/05/2021
23/05/2021
24/05/2021
25/05/2021
26/05/2021
27/05/2021
28/05/2021
29/05/2021
30/05/2021
31/05/2021
01/06/2021
02/06/2021
03/06/2021
04/06/2021
05/06/2021
06/06/2021
07/06/2021
08/06/2021
09/06/2021
10/06/2021

iventory value






















66000

daily sales
7224
5273
8632
7409
6459
6033
9711
6247
8829
5971
9115
8202
6039
7189
7067
8674
5689
8259
7423
7564
7444
6126
9038

underneath al together but that is harder for copying the raw data. 

date	iventory value	daily sales
19/05/2021		7224
20/05/2021		5273
21/05/2021		8632
22/05/2021		7409
23/05/2021		6459
24/05/2021		6033
25/05/2021		9711
26/05/2021		6247
27/05/2021		8829
28/05/2021		5971
29/05/2021		9115
30/05/2021		8202
31/05/2021		6039
01/06/2021		7189
02/06/2021		7067
03/06/2021		8674
04/06/2021		5689
05/06/2021		8259
06/06/2021		7423
07/06/2021		7564
08/06/2021		7444
09/06/2021		6126
10/06/2021	66000	9038

[Anonymous]

Hi all,

I have read the instruction as you said.

Underneath I show an example of what "what i have" and "what i need"

As you could see i got all my daily sales of the past period but i do not got my inventory value. Just the inventory value of today.

In the right part "what i need"  you could easily see that the formula is calculated iterative back in time and that i know al my inventory value of the past. 

How could you perform this task in powerbi?

I was thinking of creating a empty date table with dates ranging from t-182 to t and then iteratively filling that table, but can't figure out how that would work. 

Thanks in advance.

[parry2k]

@Anonymous how your raw data look like? Share sample data and Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490