Need to automatically distribute values between dates

[jbuck2020]

Hello,

I have a query like this

ID | Start | End | Duration | Amount 

The "Amount field" is actually supposed to be spread evenly between the start and end dates, with slightly more going proportionally to weekday dates than weekends (the weekday/weekend thing is a "want" but not a "need"). The "Duration" column is calculated based on the start and end dates. I just need to figure out how to automatically generate rows for each ID with like "Day 1, Day 2, etc" based on the duration. The end goal is to be able to report weekly totals accurately where the start and end dates span more than one week or fall between them.

I am fairly new to the PowerBI world, but I promise I looked and looked for a solution before posting here. Any help would be greatly apprectiated!

[Icey]

Hi @jbuck2020 ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[sturlaws]

Hi @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

[jbuck2020]

Thanks 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:

[jbuck2020]

Holy smokes, I think that worked! Thank you so much, this is great. 

[jbuck2020]

Just one more question: If I were trying to add another column with like Day 1, Day 2, etc next to the column we just created what would that look like in power query?

[sturlaws]

"Day " & Number.ToText(Duration.Days(Duration.From([Custom]-[start]))+1)

[jbuck2020]

Any idea why I would get this error when I apply the query changes?

OLE DB or ODBC error: [Expression.Error] The Date operation failed because the resulting value falls outside the range of allowed values..

[sturlaws]

Nope. What is you source?

Could you post a screenshot of the code you have used?

[jbuck2020]

*UPDATED* It was indeed a relationship. Once deleted, solved the problem. Now on to SUMX of distinct values, since my ID column will contain many duplicates that I do not want summed.

________________________________________________________________________________

So the above error was fixed by limiting the ids to recent (last two years') data. 

HOWEVER

Now I'm getting this error
Column 'id' in Table 'flight' contains a duplicate value '220915' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

I checked to see if there were any funky relationships that had been auto-created and elimated a few that looked wrong. But still getting this error. Not sure what the "primary key of a table" means, either.

[sturlaws]

Could you try this code:

Measure =
SUMX (
    ADDCOLUMNS ( VALUES ( 'Table'[id] ); "_tmpAmount"; MIN ( 'Table'[Amount] ) );
    [_tmpAmount]
)