Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
6 years ago
Solved

Using a table with a date ranges

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 

Need Help
  • KNP's avatar
    KNP
    6 years ago

    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

3 Replies

  • KNP's avatar
    KNP
    Super User
    6 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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/