Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Please let me know if you require further detail.
Regards,
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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
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/
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.
Please let me know if you require further detail.
Regards,
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |