Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pierre1923
New Member

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
gooranga1
Power Participant
Power Participant

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

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.