Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Filling empty rows

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/20...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-juanli-msft
Community Support
7 years ago

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