cancel
Showing results for
Did you mean:
Frequent Visitor

## Allocate value by month between start date and end date

Dear community,

I need your help on a tricky topic.

I have a really simple file with a "Start date" an "End date" a "value per day"

Start date            End date                Value per day

04-May-2019      10-July- 2019        10

I want a graph to allocate the value per day by month so that

May = (31/05 - 04/05) >> 28 * 10 = 280

June = (30/06 - 01/06) >> 30 * 10 = 300

July = (31/07 - 10/07) >> 10 * 10 = 100

Do you have any idea on how to achieve that ?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Allocate value by month between start date and end date

@pierre1923  here is how you can achieve something like this, look at page2 and power query for table2, change it as per your need

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

6 REPLIES 6
Super User

## Re: Allocate value by month between start date and end date

@pierre1923 how big of dataset you will be working with? One approach could be do create record of each date from start and end date in Power Query and then it is super easy to work with.

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Allocate value by month between start date and end date

I guess my dataset won't be that big. I was thinking about this solution but I don't know how to do so. What would be your solution ?

BR,

Pierre

Super User

## Re: Allocate value by month between start date and end date

@pierre1923 ok sending the solution on your way, stay tuned.

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

Super User

## Re: Allocate value by month between start date and end date

@pierre1923  here is how you can achieve something like this, look at page2 and power query for table2, change it as per your need

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: Allocate value by month between start date and end date

Thanks a lot @parry2k  !! I was missing the custom column >> {[StartDate]..[EndDate]}

Senior Member

## Re: Allocate value by month between start date and end date

If you have a date dimension you can use crossjoin to count number of days between dates. ValuePerDay is simply a formula to get a calculated measure for the per day costs;

`ValuePerDay = CALCULATE(max(Table22[Value Per Day]))`