Using a table with a date ranges

[bhmiller89]

I have a table that has Invoice Date. We want to divide those dates into "periods" based on specific date ranges. I am currently using a giant "IF" statement but since the periods get updated yearly an Excel spreadsheet was uploaded that includes columns 

Start Date | End Date| Period

I need a way to connect the Invoice Date to this table to associate an invoice date with a "period" depending on where it falls. 

Thanks in advance 

[Anonymous]

HI @bhmiller89,

Since we not so clear for your data structure, can you please share some dummy data with the expected result? They will be help for test and coding formulas.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

You can create a calendar table. And put your period into that. You can create a start date and end date based on period. Like for month we startofmonth function

Connect your invoice date and date of the calendar and you can plot start date ,end date  and no of invoices

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[KNP · Accepted solution]

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