Forum Discussion
Fill in blank values with previous value
Hi,
I have a table which has a value split out by day month for the full year. However, some days we have £0. This is a cumulative value.
Example output.
Day_Month. App_Value
02 Jan £100
03 Jan £250
04 Jan £0
05 Jan £300
06 Jan £400
07 Jan £0
08 Jan £0
09 Jan £450
What I'd like to do would be to populate the £0 value with the last value we have.
So my table would output like this:
Day_Month App_Value
02 Jan £100
03 Jan £250
04 Jan £250
05 Jan £300
06 Jan £400
07 Jan £400
08 Jan £400
09 Jan £450
Any idea how I could do this please?
2 Replies
Hi M_SBS_6
You can add a new column to your table using this DAX expression:
Column = Var _Val = 'Table'[App_Value] Var _Date = 'Table'[Day_Month.] Var _Date_Not_Zero =MAXX(FILTER('Table','Table'[Day_Month.]<_Date&&'Table'[App_Value]<>0),[Day_Month.]) Var _Last_Value_Not_Zero = CALCULATE(MAX('Table'[App_Value]),REMOVEFILTERS('Table'),'Table'[Day_Month.]=_Date_Not_Zero) RETURN IF('Table'[App_Value]=0,_Last_Value_Not_Zero,_Val)Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hi,
Assuming App_value is a measure that you have already written, try this
- Create a Calendar Table
- Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calender Table
- To your visual, drag Date from the Calendar Table
- Write this measure
Measure 1 = calculate([App_value],calculatetable(lastnonblank(calendar[date],calculate([App_value])),datesbetween(calendar[date],minx(all(calendar[date]),calendar[date]),max(calendar[date]))))
Hope this helps.
