cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Find Last value pr product in daily snapshot inventory

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
1 ACCEPTED SOLUTION
Microsoft Employee

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
)

Best Regards,
Angelia

Microsoft Employee

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
)

Best Regards,
Angelia