Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
M_SBS_6
Helper V
Helper V

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 2
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
VahidDM
Super User
Super User

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:

VahidDM_0-1700003085385.png

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.