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.
Hello,
I am attempting to put together some calculations and am having trouble thinking of a way to accomplish it.
I need to display the quarterly cost of a contract. My entries have a start date and an end date. The problem is that the end date sometimes goes into the next quarter. So in order for my quarterly cost to be accurate, I need to separate the entry at the end of the quarter point. I am calculating the cost by the number of weeks in the date range, multiplied by the hours per week, multiplied by the hourly rate. So I am unsure of how I can set it up so that these entries show their cost distributed across all the quarters that the entry takes place in.
@Ubertortle Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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.
Here is a screenshot of the columns I am trying to work with.
weeks between "expected start" and "expected end" * "Hours per week" * "Cost per hour" = total cost
Basically, I need to calculate the total cost for each entry/contract that I can filter by quarter. I am having trouble figuring out how to distinguish what amount of the total cost pertains to what quarter using the "expected start date" and "expected end date"
hi, @Ubertortle
Wasn't 100% clear what you were looking for, but this post should what you want
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
If not your case, please share the expected output as below:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Lin
Thank you for the replies,
So I can get my total calculated cost for each entry by calculating the DATEDIFF of "expected start date" and "expected end date" * "hours per week" * "cost per hour". As seen here
What I am attempting to do is to display these total costs in a visual, but have the total cost for each entry distributed across all quarters that fall within the date range.
I guess more specifically my issue is that I don't know how I can indicate on each entry what quarters pertain to what part of the cost.
If I pull the quarter from the "expected start date" like this
the total cost is displayed for just that quarter. The same if I pull the quarter from the "expected end date".
If I could turn each entry into something like this, I would be able to display it properly. Is there some sort of functions that will allow me to split a single entry into multiple if the date range spans over multiple quarters?
@Ubertortle based on dataset you provided, what is your expected result. Can you put this in excel with calculations you are looking for.
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.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |