cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cplesner
Helper II
Helper II

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
Microsoft

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
Microsoft

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors