Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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]))
@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]}
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |