Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filling empty rows

Hello Community,

 

I'm trying to analyze an inventroy. I have a data set like this:

 

dateSTOCK
1/01/201337135
15/01/201334435
15/01/201335245
15/01/201335290
15/01/201335740
15/01/201336640
15/01/201337090
24/01/201332725
24/01/201332950
24/01/201333400
24/01/201333445
24/01/201333895
24/01/201333940
24/01/201333985
25/01/201331375
5/02/201325840
5/02/201326380
5/02/201330880
5/02/201331330
7/02/201325165
20/02/201324760

 

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:

 

preguntaa.PNG

 

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 tableSTOCK
1/01/201337135
2/01/201337135
3/01/201337135
4/01/201337135
5/01/201337135
6/01/201337135
7/01/201337135
8/01/201337135
9/01/201337135
10/01/201337135
11/01/201337135
12/01/201337135
13/01/201337135
14/01/201337135
15/01/201334435
16/01/201334435
17/01/201334435
18/01/201334435
19/01/201334435
20/01/201334435
21/01/201334435
22/01/201334435
23/01/201334435
24/01/201332725

 

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

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

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

Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous !!!

 

the particular scenario you are showing can be solved like this:

 

add a calculated column in the Calendar Table:

 

HasStockValue = NOT( ISEMPTY( RELATEDTABLE( Stock ) ) )
 
Where Stock is the name of the Stock table
 
Then use this as measure:
 
MinStock = 
VAR CurDte = MAX( 'Calendar'[Date] )
RETURN
CALCULATE(
    MIN( Stock[STOCK] ),
    LASTDATE(
        CALCULATETABLE( 
            VALUES( 'Calendar'[Date] ),
            'Calendar'[HasStockValue] = True,
             'Calendar'[Date] <= CurDte
        )
    )
)
If you have more conditions to satisfy, please post a more representative table
 
 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

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:

 

Has stock = NOT( ISEMPTY( RELATEDTABLE( 'COMBINADO' ) ) )          COMBINADO is the table where I have my stock
 
Then created this measure:
 
MinStock2 =
                  VAR Fechactual = MAX( 'Tabla de fecha'[Date] )
                 RETURN
                 CALCULATE(
                          MIN( 'COMBINADO'[STOCK] );
                          LASTDATE(
                          CALCULATETABLE(
                                VALUES( 'Tabla de fecha'[Date] );
                                'Tabla de fecha'[Has stock] = True;
                                 'Tabla de fecha'[Date] <= Fechactual
                         )
                )
)
 
 

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] )
)
)
)

 

11.png

12.png

 

 

 

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!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.