cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ivan_xd Visitor
Visitor

Prorate Costs

Hello, here's my problem. I have three tables:

  1. Events
  2. Maintenance
  3. Fuel

Table 1 is a table of events, with the following columns: Event ID, Date, Time, Truck Number, Zone, etc

Table 2 and 3 have the following columns: ID, Truck Number, Date, Time, Cost, etc

 

I want to create two calculated columns in table 1, called "Cost of Manteinance" and "Cost of Fuel". 

 

The cost of manteinance of one event is the cost of the closest manteinance of the corresponding truck number (done before the event) divided by the number of events of the corresponding truck that are between said mantainance and the following corresponding mantainance. Tha same should be done for "Cost of Fuel".

 

I other words, I want to prorate the cost of every manteinance work (and fuel load) between the corresponding events of every truck. In the end the sum of the costs of every event should be equal to the sum of the costs of tables 2 and 3.

 

How can this be done?

 

I'm not so sure if this is the correct approach to prorating costs.  If you have any better ideas please let me know!

 

 

1 REPLY 1
CheenuSing Super Contributor
Super Contributor

Re: Prorate Costs

Hi @ivan_xd

 

Please share some data and the output expected. You can put it in OneDrive and provide the link.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (958)