Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to 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
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
User | Count |
---|---|
118 | |
75 | |
60 | |
50 | |
44 |
User | Count |
---|---|
175 | |
125 | |
60 | |
60 | |
58 |