Fill in blank value with latest value available from the same column

[Anonymous]

Hello,

 I need to fill in for the id's that have blank value, the value last available divided by 2 and then evenly distribute.Please see table below and the examples in bold:

id	day	hour	target	Target Needed
82331	5-May	22	300	300
82331	5-May	23	240	240
82331	6-May	0	120	120
82331	6-May	1	100	50
82331	6-May	2	0	16.66
82331	6-May	3	0	16.66
82331	6-May	4	0	16.66
82331	6-May	5	5	2.5
82331	6-May	6	0	2.5
82331	6-May	7	50	50
82331	6-May	8	120	120
82331	6-May	9	350	350

Is there a way to achieve this in Power bi desktop?

Thank you

[v-xulin-mstf · Accepted solution]

Hi @Anonymous

Sorry for delay reply.

Add a index column:

Try measure as:

Target Needed = 
var _curindex=CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[id]=SELECTEDVALUE('Table'[id])&&'Table'[day]=SELECTEDVALUE('Table'[day])&&'Table'[hour]=SELECTEDVALUE('Table'[hour])&&'Table'[target]=0))
var _curtarget=SELECTEDVALUE('Table'[target])
var _nextarget=CALCULATE(MAX('Table'[target]),FILTER(ALLSELECTED('Table'),'Table'[Index]=SELECTEDVALUE('Table'[Index])+1))
var _nextnot0index=CALCULATE(MIN('Table'[Index]),FILTER(ALLSELECTED('Table'),'Table'[target]<>0&&'Table'[Index]>_curindex))
var _last0index=CALCULATE(MAX('Table'[Index]),FILTER(ALLSELECTED('Table'),'Table'[Index]>=_curindex&&'Table'[target]=0))
var _preindex=CALCULATE(MAX('Table'[Index]),FILTER(ALLSELECTED('Table'),'Table'[Index]<_curindex&&'Table'[target]<>0))
var _pretarget=CALCULATE(MAX('Table'[target]),FILTER(ALLSELECTED('Table'),'Table'[Index]=_preindex))
return if(_curtarget<>0&&(_nextarget<>0||ISBLANK(_nextarget)),_curtarget,if(_curtarget<>0&&_nextarget=0,DIVIDE(_curtarget,2),DIVIDE(_pretarget,2*(if(ISBLANK(_nextnot0index),_last0index-_preindex,_nextnot0index-_preindex-1)),0)))

Here is the output:

The pbix is attached.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Anonymous]

Thank you for taking time to solve this.

😀

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBLDoAgDETvwhqTfgDhHu4MS0+g948tIS6MrQltUt600Nn3UIkZQwwEhAtkOVqQJAYIPVoK1pQMRZECJJAcgV6g9YYKaEwxMfs4+TiPMHHxu1ftdnj9W77pAnPCdpzX2191r9kYn+99UXIpu1RtSzbObvN0rd8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, day = _t, hour = _t, target = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"day", type date}, {"hour", Int64.Type}, {"target", Int64.Type}}),
    #"Grouped Rows" = Table.RemoveColumns(Table.Group(#"Changed Type", {"id", "target"}, {{"grp", each _}}, 0, (x,y) => Number.From(x[id]<>y[id] or y[target]<>0)), "target"),
    Distrubited = Table.TransformColumns(#"Grouped Rows", {"grp", each let n=Table.RowCount(_), l0=[target], l1=[hour], l2=[day] in Table.FromColumns({l2,l1} & {if List.Contains(l0,0) then {l0{0}/2} & List.Accumulate({1..n-1}, {}, (s,c) => s & {Number.Round(l0{0}/2/(n-1),1)}) else l0}, {"day", "hour","Target"})}),
    #"Expanded grp" = Table.ExpandTableColumn(Distrubited, "grp", {"day", "hour", "Target"}, {"day", "hour", "Target"})
in
    #"Expanded grp"

[Anonymous]

Is there a way to achieve this using DAX code?

Thank you!

[Anonymous]

wow, this is really complex and to be honest is hard for me to understand what you did there. could you please explain a little bit? should I add in power query a new column with your code?