Forum Discussion

wlknsn's avatar
wlknsn
Icon for Helper I rankHelper I
8 years ago

Fill empty rows/values with previous/last measure or calculated value

Hi,

 

I'm trying to find a DAX solution for a measure or calculated column which basically returns the last measure/calculated value if the cell is empty. This result should not be an aggregation, simply the last value. The point is that the values could be either measures or calculated columns which can then be used for other calculations.

 

Check below for example in terms of stock keeping.

 

 

 

PS: the totals are wrong in the last two tables. Please ignore.

 

Sample data:

 

WarehouseProductTimeStampMeasure
XA18/08/2018  09:00:004
XA18/08/2018  11:00:005
XA18/08/2018  14:00:009
XA18/08/2018  16:00:007
XB18/08/2018  08:00:002
XB18/08/2018  13:00:005
XB18/08/2018  15:00:006
XB18/08/2018  16:00:002
YA18/08/2018  07:00:008
YA18/08/2018  11:00:008
YA18/08/2018  14:00:004
YB18/08/2018  07:00:009
YB18/08/2018  08:00:002
YB18/08/2018  13:00:001
YB18/08/2018  15:00:009
YB18/08/2018  17:00:006

 

Many thanks!

Need Help

4 Replies

    • wlknsn's avatar
      wlknsn
      Icon for Helper I rankHelper I
      8 years ago

      As mentionned before, I need to fill this based on a measure. The power query solution is based on data, and not a measure/calculation. Same for the dax solution, that will not work with measure. It's the last measure that needs to be filled.

      • Yggdrasill's avatar
        Yggdrasill
        Icon for Responsive Resident rankResponsive Resident
        7 years ago

        I've used this as a calculated column

         

        Fill empty rows = 
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( dimDates[Date]; 1 );
        FILTER (
            ALL ( dimDates );
            dimDates[Date]<= EARLIER (  dimDates[Date] )
                && NOT ( ISBLANK ( dimDates[YourColumnWithBlankRows]) )
        )
    )
RETURN
    CALCULATE (
        SUM ( dimDates[YourColumnWithBlankRows] );
        FILTER ( ALL ( dimDates ); dimDates[Date] = LastNonBlankDate )
    )
  • yosytrejo001's avatar
    yosytrejo001
    Regular Visitor
    5 years ago

    Hi friends, 
    I'm looking for a solution  to my problem, i'm trying to obtain the previous value for each date. It should be a calculated column because i'll use it in DISTINCTCOUNTNOBLANK() function and it requires a column.

    I'm a Spanish speaker, my apologizes.
     Traducción: Busco una manera de obtener el valor anterior para cada una de las fechas que se encuentran en mi tabla calendario. Necesito una columna calculada ya que lo usaré en la función DISTINCTCOUNTNOBLANK(). 


    Tabla - Stock Histórico - All movements of stock by product, warehouse and date. Here we have the stock only if an in or out of stock happened in that date.

    Tabla - Calendario : All dates in my calendar

    Resultado Deseado: I want to obtain a the stock in all dates in my calendar, if it's in blank it should be filled by the previous value.


    De antemano agradezco el apoyo y adjunto el archivo Test. https://mistrperu-my.sharepoint.com/:u:/g/personal/yosy_trejo_mistr_com_pe/EZk1yjmAcqdFv14U5gNGeoEByVNQngAdBL74L53In-XIhQ?e=BttBRH