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:

 

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:

 

 

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

Need Help

6 Replies

  • 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 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