Fill Between Date Points

[JP8991]

Hello, 

The below table is filtered on one CentreCode (for ease of explanation) and shows some data.

The below table illustrates what I am after, ideally I would do a transformation on Supply Licensed Places to do away with the need for an extra Cumulative column. The blue highlights are the lines taken from the table above. As you will notice the missing dates between each of the months have now been filled in.

Thanks,

[Greg_Deckler]

Inventing data is hard. If you had your data pivoted and actually had all of the dates you could use Fill Down. @ImkeF ?

[ImkeF]

Hi

please paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByJDMyUdJUNjY3OlWJ1oJUMjuJiJAVjEwBIsYg5SZQAVMgYLWYCETCyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    AddNextDate = Table.AddColumn(#"Added Index", "NextDate", each #"Added Index"{[Index]+1}[Date], type date),
    #"AddMonth#" = Table.AddColumn(AddNextDate, "Months#", each Number.Round ( Number.From ( [NextDate] - [Date]) / 30 ) ),
    AddMonths = Table.AddColumn(#"AddMonth#", "Months", each {0..[#"Months#"]-1}),
    #"Replaced Errors" = Table.ReplaceErrorValues(AddMonths, {{"Months", null}}),
    #"Expanded Months" = Table.ExpandListColumn(#"Replaced Errors", "Months"),
    newDate = Table.AddColumn(#"Expanded Months", "newDate", each try Date.AddMonths([Date], [Months]) otherwise [Date]),
    newValue = Table.AddColumn(newDate, "ValueNew", each if [newDate] = [Date] then [Value] else 0),
    #"Removed Other Columns" = Table.SelectColumns(newValue,{"newDate", "Value", "Date", "ValueNew"})
in
    #"Removed Other Columns"

The formula to fetch the value from the next row is simple, but not the fastest. So if you run into performance problems, you should use this approach instead: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po... 

[JP8991]

@ImkeF 

Thank you so much for this, excellent code.

Just a few questions/tweaks.

• Would it be possible to have the newDate column go down to the current month i.e. 01/11/2019?
• Would it be possible to have the Value column cumulative so that when the 40, 100 and 148 appear the repeated values are 1377, 1477 and 1625 respectively?
• In my example I have filtered on one centre code, as you have used an index column how would I replicate this outcome when the rest of the data set is used as I am guessing the index would need to start counting from zero for each Centre Code?

[dax · Accepted solution]

Hi JP8991, 

You could refer to my sample to see whether it work or not. Use Query 1 , Query2, Merge 1 and Query3.

Best Regards,
Zoe Zhi

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

[JP8991]

@dax 

Awesome, I am pretty sure this will do the job, I am just implementing it into my query but I am pretty sure it will work.

Thanks so much for your help.