cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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.

Frequent Visitor

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"

Community Support

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

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

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

Frequent Visitor

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?

Super User

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