Find Last value pr product in daily snapshot inventory

[cplesner]

Hi

I have a bit of a challenge. Various systems are feeding inventory data for specific products. Feeds are not always providing the daily inventory, so I need to somehow always show the "latest" value pr product for any given date.

So in my simple data model i have a product table, a data table and an inventory table. Below is the fact table (snapshot inventory count for the days they are feed in). Blank means no data have arrived while 0 means that the inventory for that product have gone to zero.

Now i would like to have a measure that always gives latest value looking at day as well as month level. For daily level i would like to see the following

On the month level i have the following calculation that works fine as there are always some data for he product for some day during the month.

Inventory =
SUMX (
    VALUES ( Product[ProductName] );
    CALCULATE (
        SUM ( Inventory[InventoryValue] );
        LASTNONBLANK (
            'Date'[Date];
            CALCULATE ( COUNTROWS ( Inventory ) )
        )
    )
)

Also aftaer having that figure I need it to calculate daily changes in the inventory, so that below figures can be found pr day.

So a blank entry, means no change while 0 means that the product inventory actively have changed to 0.

Any bright ideas ?

Below is sample data

Date	Product	Inventory
06-01-2018	A	100
07-01-2018	A	121
08-01-2018	A	125
09-01-2018	A	130
10-01-2018	A	105
11-01-2018	A	110
15-01-2018	A	121
16-01-2018	A	125
17-01-2018	A	130
19-01-2018	A	0
07-01-2018	B	325
08-01-2018	B	330
09-01-2018	B	340
10-01-2018	B	310
11-01-2018	B	330
12-01-2018	B	300
13-01-2018	B	325
14-01-2018	B	330
15-01-2018	B	340
16-01-2018	B	310
17-01-2018	B	330
18-01-2018	B	300
19-01-2018	B	325
20-01-2018	B	330

[v-huizhn-msft · Accepted solution]

Hi @cplesner,

First, I create a calendar table, and get related A and B column, then create calculated column to get the change column using the formula.

changeA =
IF (
    'Date'[A] <> BLANK (),
    'Date'[A]
        - LOOKUPVALUE ( 'Date'[A], 'Date'[index], 'Date'[index] - 1 ),
    0
)


changeA =
IF (
    'Date'[A] <> BLANK (),
    'Date'[A]
        - LOOKUPVALUE ( 'Date'[A], 'Date'[index], 'Date'[index] - 1 ),
    0
)

You will get expected result as follows. Please download the .pbix file for further analysis.



Best Regards,
Angelia