cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JBikker Frequent Visitor
Frequent Visitor

Costs / daycount

Hi there,

I'm just starting with Power BI and can't figure out the following.

 

I have a daycount colomn and a costs colomn. I want to device costs by total daycount. After that I want to multiply 'costs per daycount' times daycount. In the snip below the result for the top row should be 236.30 (375/146* 92)and the botom row 138.70.

 

I calculated daycount: Daycount = DATEDIFF(MIN([Start Date];[Einddatum]);MAX([start Date];[Einddatum]);DAY) 

Costs 2018 is a colomn in anonther query.

 

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Costs / daycount

Hi @JBikker,

 

Here we can take the following steps to meet your requirement.

 

1. Add a calculated column to get the sum of Daycount per contract.

 

Sum = CALCULATE(SUM(Subscriptions[Daycount]),FILTER(ALL(Subscriptions),Subscriptions[Part of contract]=EARLIER(Subscriptions[Part of contract])))

2. Then can need to update the formula as below.

 

Result = RELATED(Contracts[Costs 2018]) / Subscriptions[Sum] * Subscriptions[Daycount]

1.png

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/ofd8q15osojrozr/prev2.pbix?dl=0

 

Regards,

Frank

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

Re: Costs / daycount

Hi @JBikker,

 

As your description, I made a sample here. As you said Costs 2018 is a column in another query. To achieve your goal here, the two tables should be related. So I created relationship between the DATA and cost tables based on index columns in advance.

 

1.png

 

Then we can create the calculate column in Data table using the formula.

 

Result = RELATED(Cost[Cost 2018])/SUM(DATA[Daycount])*DATA[Daycount]

Then we can get the result as we excepted.

 

2.png

 

For more details, please check the pbix as attached. If this doesn’t meet your requirement, kindly share you sample data to me please.

 

https://www.dropbox.com/s/401oxqjtl2uq4jv/Costsdaycount2.pbix?dl=0

 

Regards,

Frank

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

Re: Costs / daycount

@v-frfei-msftThanks so much for your help! I'm getting closer.  I have two followup questions.

 

1. How can you calculate daycount if there is no enddate yet? If the enddate is blank I would like to get the result Startdate > Today

2. 'Price' is not always the same. Subscriptions are part of different contract.  When I used your method in my model it did not filter per contract but instead summed up all 'daycount's from different contracts.

 

I've attached a file.. Hopefully this makes it a bit more clear. The column result does not make any sense.

 

https://wetransfer.com/downloads/2c34615a0ebc0ae4241048b8e0e3fc5d20180807152428/d0cb3c5ac7d1392ce5ee...

Community Support Team
Community Support Team

Re: Costs / daycount

Hi @JBikker,

 

Here we can take the following steps to meet your requirement.

 

1. Add a calculated column to get the sum of Daycount per contract.

 

Sum = CALCULATE(SUM(Subscriptions[Daycount]),FILTER(ALL(Subscriptions),Subscriptions[Part of contract]=EARLIER(Subscriptions[Part of contract])))

2. Then can need to update the formula as below.

 

Result = RELATED(Contracts[Costs 2018]) / Subscriptions[Sum] * Subscriptions[Daycount]

1.png

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/ofd8q15osojrozr/prev2.pbix?dl=0

 

Regards,

Frank

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

Re: Costs / daycount

Hi @JBikker,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case please.

 

Regards,

Frank

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

Re: Costs / daycount

Hi Frank, Yes it does make sense. Thanks so much! I tried to close it a couple of times now but I keep getting the following error. I tried different browser but I keep getting the error... JavaScript is on...

Authentication failed for the action you are trying to do. This failure could be due to your browser not supporting JavaScript, JavaScript not being enabled, or trying to use the action URL directly in the browser address bar instead of clicking the link on the page. 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 108 members 1,586 guests
Please welcome our newest community members: