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
Ubertortle
Frequent Visitor

Complex calculation

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.

6 REPLIES 6
parry2k
Super User
Super User

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

 

table.PNG

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

https://community.powerbi.com/t5/Desktop/Track-the-Workload-by-counting-the-number-of-in-progress-ta...

 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 calculated.PNG

 

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.

graph.PNG

 

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

quart.PNG

the total cost is displayed for just that quarter. The same if I pull the quarter from the "expected end date". 

 

somthing.PNG

 

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.

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.