Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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...
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.
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.
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 ) )