Fill in blank values with previous value

[M_SBS_6]

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? 

[Ashish_Mathur]

Hi,

Assuming App_value is a measure that you have already written, try this

1. Create a Calendar Table
2. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calender Table
3. To your visual, drag Date from the Calendar Table
4. 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.

[VahidDM]

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

LinkedIn | Twitter | Blog | YouTube