How to calculate the value of customer stock

[Boegebjerg]

Hello PBI community.

I am currently facing an issue about how to correctly perform a specific dax calculation. I have made a dummy table (see screenshots) on the data structure, and the outcome I wish to achieve. If you want me to upload it as a .pbix file let me know.

Context:

Figure 1.

In Figure 1, I have three different customers, who have bought a product at different quantities, with different unit prices, at different times. In my business context, it is important to understand that customers can buy a product in advance before they consume it e.g. the first transaction shows a purchase of 10 units, where the customer does not have to consume the product immediately, but can keep it as "stock" for X period of time. 

Figure 2.

This brings us to Figure 2, where we see the current inventory/"stock" of a customer. 

The problem:
Lets take Customer 1 (C1) as an example. C1 has two transcations, one on 1-1-2020 of 10 units, another on 1-20-2020 of 30 units. At the current date (say 07-07-2020) C1 has 34 remaining products. We assume a 'first-in-first-out' scenario, where C1 has used 6 products (40-34=6) from the first transaction. As the two transactions have different unit prices, my goal is to figured out the value of the remaining stock of the customer.

Figure 3.

Here I have greyed out the two transactions for C1. The customer has used 6 products, and has 34 left. As we assume 'first-in-first-out', the 6 products used will be from the first transaction.

The value of C1's stock will then be the [quantity left from each transaction] * [unit price for each transaction]. For C1, the value of the stock is 4*200 + 30*166,67.

The end table I want to achieve will look like this:

How do I achieve this results with DAX in Power BI?
To reiterate: I want to calculate the value of a customer's stock, but take into consideration that the unit price of each transaction is different, while incorporating the 'first-in-first-out' principle. 

Hope there are some brilliant minds out there who can help me with this issue!

/Boegebjerg

[AllisonKennedy · Accepted solution]

Pbix file is always helpful, especially if you have it already made with dummy data.

If all you need is the total remaining value, you might swap your thinking around and approach the problem as a 'last-in-last-out' principle.

I have expanded your sample data a bit for CustomerX, who has 24 qty left:  

Order Number	Qty Bought	A) Total Qty Left	B) Running Total Qty Bought	C) Total Qty OnSold	D) RunningTotal Remain	E) Replace Negative with 0	F) Stock Remaining
1	10	24	10	16	-6	0	0
2	15	24	25	16	9	9	9
3	7	24	32	16	16	16	7
4	8	24	40	16	24	24	8

My thought process is: 

A) Don't need this as a column, I just put it there for reference as you already know it. 

B) Use SUMX(FILTER(ALL(Table), EARLIER(OrderID)<= OrderID))

C) Again, you basically have this info by doing MAX(RunningTotal)-[QtyLeft]

D) B-C

E) IF( (B-C)<= 0, 0, B-C) 

or IF(D<= 0, 0, D)

F) Use EARLIER again to reverse the RunningTotal calculation.

If you provide sample pbix, the community can give you more specific help, but hopefully that gets you started on the theory.

[AllisonKennedy · Accepted solution]

Pbix file is always helpful, especially if you have it already made with dummy data.

If all you need is the total remaining value, you might swap your thinking around and approach the problem as a 'last-in-last-out' principle.

I have expanded your sample data a bit for CustomerX, who has 24 qty left:  

Order Number	Qty Bought	A) Total Qty Left	B) Running Total Qty Bought	C) Total Qty OnSold	D) RunningTotal Remain	E) Replace Negative with 0	F) Stock Remaining
1	10	24	10	16	-6	0	0
2	15	24	25	16	9	9	9
3	7	24	32	16	16	16	7
4	8	24	40	16	24	24	8

My thought process is: 

A) Don't need this as a column, I just put it there for reference as you already know it. 

B) Use SUMX(FILTER(ALL(Table), EARLIER(OrderID)<= OrderID))

C) Again, you basically have this info by doing MAX(RunningTotal)-[QtyLeft]

D) B-C

E) IF( (B-C)<= 0, 0, B-C) 

or IF(D<= 0, 0, D)

F) Use EARLIER again to reverse the RunningTotal calculation.

If you provide sample pbix, the community can give you more specific help, but hopefully that gets you started on the theory.

[Boegebjerg]

Okay that was a clever approach! I did not consider that perspective.
I have now tried to implement your step-by-step guide in DAX, but I cannot seem to get it to work. Not sure what I am doing wrong.

Here is the link for the .pbix file: @https://drive.google.com/file/d/1nZWDkptAVuyCx59kSO1M0VhtT6Vw2zqq/view?usp=sharing
I would appreciate if you or any other fellow PBI'er could help me put your suggestion into action.

Thanks in advance.

/Boegebjerg

[AllisonKennedy · Accepted solution]

@Boegebjerg  I have added the columns to your sample file, please find the updated version here: https://acetraining-my.sharepoint.com/:f:/g/personal/allison_kennedy_ace_co_nz/EuUa53fdcDRKkRJvM0XzI...

[v-yingjl]

Hi @Boegebjerg ,

Based on FIFO principle, I created a [FIFO left] column to calculate the actual left count:

FIFO left = 
VAR _Date = [Date]
VAR _cid = [Custormer id]
VAR _cqs =
    SUMX (
        FILTER ( 'Table', 'Table'[Date] <= _Date && 'Table'[Custormer id] = _cid ),
        [Quantity sold]
    )
VAR _b =
    CALCULATE (
        SUM ( 'Table'[Quantity left] ),
        ALLEXCEPT ( 'Table', 'Table'[Custormer id] )
    )
VAR _c =
    CALCULATE (
        SUM ( 'Table'[Quantity sold] ),
        ALLEXCEPT ( 'Table', 'Table'[Custormer id] )
    )
VAR _a = _c - _b
VAR tab =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Date],
            'Table'[Custormer id],
            'Table'[Unit price],
            'Table'[Quantity left]
        ),
        "left", IF ( _a > _cqs, 0, _cqs - _a )
    )
RETURN
    MAXX ( tab, [left] )

Then create another calculated column to calculate remain values:

Remaining values = 
VAR _q =
    IF (
        [FIFO left]
            = CALCULATE (
                MAX ( 'Table'[FIFO left] ),
                ALLEXCEPT ( 'Table', 'Table'[Custormer id] )
            ),
        [Quantity sold],
        IF (
            [FIFO left] = 0,
            0,
            VAR _d = [Date]
            VAR _c = [Custormer id]
            RETURN
                [FIFO left]
                    - MAXX (
                        FILTER (
                            ALL ( 'Table' ),
                            [Custormer id] <= _c
                                && [Date]
                                    = CALCULATE (
                                        MAX ( 'Table'[Date] ),
                                        FILTER ( ALL ( 'Table' ), [Date] < _d && [Custormer id] = _c )
                                    )
                        ),
                        [FIFO left]
                    )
        )
    )
RETURN
    _q * [Unit price]

The result will be like this:

Attached my sample file that hopes to help you: remaining values.pbix

Best Regards,
Yingjie Li

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