Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Community,
I'm trying to analyze an inventroy. I have a data set like this:
date | STOCK |
1/01/2013 | 37135 |
15/01/2013 | 34435 |
15/01/2013 | 35245 |
15/01/2013 | 35290 |
15/01/2013 | 35740 |
15/01/2013 | 36640 |
15/01/2013 | 37090 |
24/01/2013 | 32725 |
24/01/2013 | 32950 |
24/01/2013 | 33400 |
24/01/2013 | 33445 |
24/01/2013 | 33895 |
24/01/2013 | 33940 |
24/01/2013 | 33985 |
25/01/2013 | 31375 |
5/02/2013 | 25840 |
5/02/2013 | 26380 |
5/02/2013 | 30880 |
5/02/2013 | 31330 |
7/02/2013 | 25165 |
20/02/2013 | 24760 |
I only consider the MIN value for each day since it's how the day ended,
I have made a relationship with a date table but since there are dates with no data on this data set, I can't really get an accurate histogram, it looks like this:
Is there a way I can get the minimum value for each day and fill the blank dates between 2 stock movements with that minimum value, it would like this:
date table | STOCK |
1/01/2013 | 37135 |
2/01/2013 | 37135 |
3/01/2013 | 37135 |
4/01/2013 | 37135 |
5/01/2013 | 37135 |
6/01/2013 | 37135 |
7/01/2013 | 37135 |
8/01/2013 | 37135 |
9/01/2013 | 37135 |
10/01/2013 | 37135 |
11/01/2013 | 37135 |
12/01/2013 | 37135 |
13/01/2013 | 37135 |
14/01/2013 | 37135 |
15/01/2013 | 34435 |
16/01/2013 | 34435 |
17/01/2013 | 34435 |
18/01/2013 | 34435 |
19/01/2013 | 34435 |
20/01/2013 | 34435 |
21/01/2013 | 34435 |
22/01/2013 | 34435 |
23/01/2013 | 34435 |
24/01/2013 | 32725 |
I'm really struggling with this. Please consider I have several items in stock and would like to see the inventory levels over the years of each individual item.
Thanks in advance
Hi @Anonymous
As tested, Ashish_Mathur and LivioLanzo's solutions are both helpful, could you have a try on your site and check if it suits your situation?
If you have any question, please don't hesitate to ask me.
Best Regards
Maggie
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Anonymous !!!
the particular scenario you are showing can be solved like this:
add a calculated column in the Calendar Table:
MinStock = VAR CurDte = MAX( 'Calendar'[Date] ) RETURN CALCULATE( MIN( Stock[STOCK] ), LASTDATE( CALCULATETABLE( VALUES( 'Calendar'[Date] ), 'Calendar'[HasStockValue] = True, 'Calendar'[Date] <= CurDte ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks for your quick response, I tried it out, doesn't seem to do the trick, this is what I get:
MinStock Date
12,995.00 martes, 1 de enero de 2013
2,488.00 martes, 15 de enero de 2013
660.00 jueves, 24 de enero de 2013
5,060.00 viernes, 25 de enero de 2013
5,000.00 martes, 5 de febrero de 2013
4,400.00 jueves, 7 de febrero de 2013
0.00 miércoles, 20 de febrero de 2013
907.00 miércoles, 27 de febrero de 2013
6,187.00 jueves, 28 de febrero de 2013
It goes from 1 january to 15 january and so on
I created a calculated column with this formula on the date table:
Hi @Anonymous
As tested, Ashish_Mathur's solution is perfer to refer to for you.
Create measures
Min stock =
IF (
ISBLANK ( MIN ( Data[STOCK] ) ),
LASTNONBLANK ( 'Calendar'[Date], MIN ( Data[STOCK] ) ),
MIN ( Data[STOCK] )
)
Min stock final =
CALCULATE (
[Min stock],
DATESBETWEEN (
'Calendar'[Date],
IF (
ISBLANK ( [Min stock] ),
CALCULATE (
LASTNONBLANK ( 'Calendar'[Date], [Min stock] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) )
),
MIN ( 'Calendar'[Date] )
),
IF (
ISBLANK ( [Min stock] ),
CALCULATE (
LASTNONBLANK ( 'Calendar'[Date], [Min stock] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) )
),
MIN ( 'Calendar'[Date] )
)
)
)
Best Regards
Maggie
@Anonymous
Whcih date column are you dropping on the rows section of the matrix visual?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |