The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Hi,
Assuming App_value is a measure that you have already written, try this
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.
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!!