Allocating production quantities for projects to weeks

[bgood]

Hi Sam,

I have a requirement from an internal stakeholder to replicate a graph that he has in a spreadsheet. He needs to allocate project quantities into weeks between two dates. The basic layout of the data is:

Currently the allocation happens across the spreadsheet. The formula to do so is pretty easy (the calculated weekly qty is allocated across the number of calculated weeks from the start date week), i’m just not sure how this would be implemented in a table within Power BI. Is this a possibility? Would it be better to use measures and if so what would the approach be.

I’m not sure if I’ve provided enough information or if my image will be visible, so please let me know so i can try and explain better.
Many thanks,
Bronwyn

[BrianJ]

@bgood,

Here’s a link where @sam.mckay worked out a solution to what looks like a very comparable problem:

Allocating Order Units by Start and End Dates

Hello, I have a fact table that resembles the below structure: Customer Order Number Start Date End Date Units Order Amount Soccer Pro XYZ ABC123 1/1/2013 2/5/2013 50 5000 An order/contract might have a start and end date spanning between a few months. What I’d like to be able to do is filter to a month, say Jan 2013, and see how many units fall within that month (could do the same for dollars by dividing units and order amount). Ex. a pivot table Month | Units Ordered | Order Amo…

This should provide a good starting point.

• Brian

[bgood]

Thanks @BrianJ I’ll give it a good go

[EnterpriseDNA]

To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!