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
LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

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'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
                             )
                    )
    )
     
     
    • LivioLanzo's avatar
      LivioLanzo
      Solution Sage
      7 years ago

      Anonymous

       

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

    • 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