Filling empty rows

[Anonymous]

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

[v-juanli-msft]

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]

Hi,

You may download my PBI file from here.

Hope this helps.

[LivioLanzo]

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

[Anonymous]

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

                         )

                )

)

[v-juanli-msft]

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

[LivioLanzo]

@Anonymous

Whcih date column are you dropping on the rows section of the matrix visual?