Forum Discussion
Helper INeed to automatically distribute values between dates
Resident RockstarHi jbuck2020,
if you want to have them as rows in a table i Power BI, you should use Power Query. Add a custom column and use this code
= Table.AddColumn(#"Changed Type", "Custom", each { Number.From([Start])..Number.From([End]) })
The resulting column will be a list. Expand the list, and reformat it as date. If you want to have it like day 1, day 2 relative to the start date, create a new column like this, and work from there
=Duration.Days([Custom]-[Start])+1
The other option is to have the rows existing only in query time, which means, they only exists when a measure is evaluated. The code for this more complex, let me know if that is what you are after.
Cheers,
Sturla
Helper IThanks for you help! This code gives me a table, and when I expand it to a list and reformat to date I am winding up with a seemingly random week of dates, which repeats over and over and kind of breaks my query.
What I am trying to get is this:
id | start | end | duration | custom.date | custom.day
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 1
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 2
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 3
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 4
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 5
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 6
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 7
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 8
2924 | 5/24/2010 | 6/5/2010 | 13 | 5/24/2010 | day 9
etc and then for the next ID it might have a duration of 3 days so only 3 rows, etc
But here's what the code you suggested generated:
id | start | end | duration | custom
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/07/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/08/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/09/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/10/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/11/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/12/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/13/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/07/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/08/2020
2924 | 5/24/2010 | 6/5/2010 | 13 | 7/09/2020
etc
Any ideas why?
And perhaps it would be helpful to explain more about my data.Each ID has a dollar amount attached to it that I need to be able to allocate to weekly totals. Currently I have one row per ID, so it puts all of the dollar amount on either the start date or the end date in a powerbi visualization. I have thousands of different IDs, all with different dollar amounts and start/end dates that I need to be able to visualize in a weekly format that begins on Tuesdays and ends on Mondays. I have figured out how to make custom weeks, but still can't get powerquery to split the IDs up into days based on the start/end specified dates. Quite the conundrum.
- sturlaws
sorry, gave you the wrong code, try this instead: