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.
Hi
suppose i have a table with 3 fields - ID, StartDate, EndDate
suppose 1 | 1/4/2017 | 4/15/2017
Is there a way to use that to create monthly row data for each month in the time frame? would be great if start/end align so something like this:
ID | Date
1 | 1/4/2017
1 | 2/28/2017
1 | 3/31/2017
1 | 4/15/2017
Solved! Go to Solution.
Review the function by @ImkeF at https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-.... Possibly this is the solution you need.
Proud to be a Super User!
I would try using the Unpivot function. You'll find it in the transform tab of the ribbon menu.
but would unpivot be able to create the rows for the months between start and end dates? wouldn't that only create two rows (start end)
Review the function by @ImkeF at https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-.... Possibly this is the solution you need.
Proud to be a Super User!
Please try this formula ( change to adapt with your source):
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [EndDate]-[StartDate],Int64.Type), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each Number.IntegerDivide([Custom],30)+1), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Repeat(#"Changed Type1"[StartDate],[Custom.1])), #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"), #"Added Index" = Table.AddIndexColumn(#"Expanded Custom.2", "Index", 1, 1), #"Added Custom3" = Table.AddColumn(#"Added Index", "Custom.3", each if [Index]=1 then [StartDate] else if [Index]=[Custom.1] then [EndDate] else Date.EndOfMonth(Date.AddMonths([StartDate],[Index]-1))), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"ID", "Custom.3"}) in #"Removed Other Columns"
Hope it helps...
Ninter
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |