Is there a way to write a calculation that will find the related time period and return a cost.
Table 1. Contract (Lookup Table)
A. Has Contract Number
B. Has Location
Table 2. Sales Order. (Lookup Table)
A. Has Contract Number (Has a primary join from tables 1 to 2 - "one-to-many" relationship)
B. Has Sales Order Date
C. Has Sales Order Number
Table 3. Transactions Table (Fact Table)
A. Has Invoice Number (Has a primary join from tables 2 to 3 - "Currently has a secondary "many-to-many" relationship)
B. Has a Transaction Date
B. Has a cost
C. Has a Location
Table 4. Locations (Lookup Table)
A. Has list of Locations (Has a primary join from tables 4 to 3 - "one-to-many" relationship &
This also has a secondary join from table 4 to 1 - "one-to-many" relationship)
The costs are in Table 3. These costs change periodically and change between location.
I wrote a measure:
Cost per site =
CALCULATE( [Cost],
TREATAS( VALUES( 'Transaction Table'[Location] ), Contract Table[Location] ) ) )
But I don't think it is calculating correctly.
To increase the complexity, I need to be in the context of the Contract Number. So the cost depends on two factors.
1st. Location
2nd. Time Period
Basically the measure I am trying to create is:
Calculate the cost for a contract when the related Location matches the contract, and when the Sales Order matches the related Date-Time Frame.
The Time Frame is basically 1 month.
So an example would be say I have a contract 00001. The contract is in Michigan. I have 3 sales orders.
The first Sales order is in Michigan (But this is based off the contract location, not the sales order) and has a date of 2/3/2021. So I want to take my cost in Michigan and filter it for the month of February 2021.
The second Sales order has a date of 3/15/2021, and the third has a date of 4/25/21.
Each sales order has a different cost based on month the sales order was written, but the cost is being filtered by the contract location to get the related cost location.
I don't have any sample data prepared, as I thought this might be an easy double filter calculation using related, but now that it is all written out it seems a little more difficult.
Any thoughts? Thank you.
