Complex calculation

[Ubertortle]

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.

[parry2k]

@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

[Ubertortle]

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"

[v-lili6-msft]

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

[Ubertortle]

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

[Ubertortle]

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?

[parry2k]

@Ubertortle based on dataset you provided, what is your expected result. Can you put this in excel with calculations you are looking for.