Skip to main content
cancel
Showing results for 
Search instead 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

Reply
cplesner
Helper III
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.

 

Source.PNG

 

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

Calc.PNG

 

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.

diff.PNG

 

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

DateProductInventory
06-01-2018A100
07-01-2018A121
08-01-2018A125
09-01-2018A130
10-01-2018A105
11-01-2018A110
15-01-2018A121
16-01-2018A125
17-01-2018A130
19-01-2018A0
07-01-2018B325
08-01-2018B330
09-01-2018B340
10-01-2018B310
11-01-2018B330
12-01-2018B300
13-01-2018B325
14-01-2018B330
15-01-2018B340
16-01-2018B310
17-01-2018B330
18-01-2018B300
19-01-2018B325
20-01-2018B330
1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft Employee
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
)


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

1.PNG

Best Regards,
Angelia


View solution in original post

1 REPLY 1
v-huizhn-msft
Microsoft Employee
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
)


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

1.PNG

Best Regards,
Angelia


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors