Using a table with a date ranges

Hi bhmiller89 ,

One possible way to deal with this is to expand the table that has the periods in it. 

As I wasn't sure what your table looked like, I created a period table...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0lYyDHGMYxVIrVgcibwOUN9A3MYBwjuLw5srwlnAOTNzRAMt/QCMYxUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Period = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Period", Int64.Type}})
in
    #"Changed Type"

Then expanded the period table...

let
    Source = Period,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {Number.From([Start])..Number.From([End])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type"

Then merged Invoice table with period, joining on the date columns...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcqxDcAgDATAXVwj/b8JiMyC2H+NSGCnoj3dnEaBDqfTiom0VQ7WG7bEJ1AV7P9MHeCb2MKckBL71vUB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, PeriodTableExpanded, {"Custom"}, "PeriodTableExpanded", JoinKind.LeftOuter),
    #"Expanded PeriodTableExpanded" = Table.ExpandTableColumn(#"Merged Queries", "PeriodTableExpanded", {"Period"}, {"Period"})
in
    #"Expanded PeriodTableExpanded"

Here is a link to the PBIX file for your reference. 

PBIX File 

Please let me know if you require further detail.

Regards,

Kim