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

[wlknsn]

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:

Warehouse	Product	TimeStamp	Measure
X	A	18/08/2018  09:00:00	4
X	A	18/08/2018  11:00:00	5
X	A	18/08/2018  14:00:00	9
X	A	18/08/2018  16:00:00	7
X	B	18/08/2018  08:00:00	2
X	B	18/08/2018  13:00:00	5
X	B	18/08/2018  15:00:00	6
X	B	18/08/2018  16:00:00	2
Y	A	18/08/2018  07:00:00	8
Y	A	18/08/2018  11:00:00	8
Y	A	18/08/2018  14:00:00	4
Y	B	18/08/2018  07:00:00	9
Y	B	18/08/2018  08:00:00	2
Y	B	18/08/2018  13:00:00	1
Y	B	18/08/2018  15:00:00	9
Y	B	18/08/2018  17:00:00	6

Many thanks!

[yosytrejo001]

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/EZk1yjmAcqdFv14U5gNGeoEBy... 

[Seward12533]

While its possible in DAX - seethis solution https://community.powerbi.com/t5/Desktop/Fill-down-collumn-information-based-on-condition/td-p/43946...

If all your values are numeric then you can use the Fill Option in the Query Editor that will replace blanks with the values in previous rows within columns.

[wlknsn]

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]

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