cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pierre1923 Frequent Visitor
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 ?

Thanks a lot in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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





Did I answer your question? Mark my post as a solution.

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




 




6 REPLIES 6
Super User
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.





Did I answer your question? Mark my post as a solution.

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




 




pierre1923 Frequent Visitor
Frequent Visitor

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

@parry2k  thanks for your answer.

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
Super User

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

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





Did I answer your question? Mark my post as a solution.

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




 




Super User
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





Did I answer your question? Mark my post as a solution.

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




 




pierre1923 Frequent Visitor
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]}

gooranga1 Senior Member
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]))

 

CountingDays.PNG