Related Date Time Frame and Location

DAX DAX Calculations
1

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. I tried to put something simple together.

Any thoughts? Thank you.
Related Date Time.xlsx (21.8 KB)
Related Date Time and Location.pbix (78.3 KB)

2

Bumping this post for more visibility.

3

Hi @ibesmond

I have gone through the post. Your Data Model is clear but example you given is not as per sample data in post. Without Sample data and expected output it will be difficult to understand what you are trying to achieve.

If you can add some proper sample data based on the example you gave and expected outcome, then we may be able to help further.

Thanks
Ankit J

1 Like
4

Hi @ibesmond, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

@ankit
Hopefully this will make it a little clearer what I am trying to do.

I believe my previous formula has been corrected to return the location costs, but now I’m trying to narrow down the costs by month and associate the monthly costs based on the date of the sales orders.

Let me know if this helps. Thank you,

Related Date Time.xlsx (21.8 KB)
Related Date Time and Location.pbix (89.0 KB)

6

@EnterpriseDNA I added some explanation and detail to my query in attempts to make my desired goal clearer.

7

Some considerations:
How should Costs from the Transactions Table be split to contracts, if there is no direct cost split to a contract (no contract information in this table), and furthermore there are more contracts per location, so which part of the costs should go to which contract?
image

Would it be possible to use the invoice number which appears in both tables (SalesT and TransactionsT) for the cost calculations of the sales ?

Total costs Invoices = CALCULATE( [Total Costs], 
                TREATAS(VALUES(SalesT[Invoice Nr Sales]), TransactionsT[Invoice Nr Costs]))

See detail results the first screenshot: (filtered on contract 5 and location MI in Sales table, with Transaction Table for comparison, filtered on location MI).
Apparently costs are made earlier or even later as the sales are made, see also first screenshot.

Costs could be summarized per contract and per month with the formula above, by adding an calculated column Year-Month in the SalesT, based upon the sales order date, see second screenshot below.

Hopefully this answers your question.

See PBIX
Related Date Time Frame and Location v4.pbix (91.0 KB)

image
image1160×403 26.8 KB

image
image1034×437 26.2 KB

2 Likes
8

Hi @ibesmond did the response provided by @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

9

Hi @ibesmond, we’ve noticed that no response has been received from you since July 13th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

10

@deltaselect
I’ll give it a shot. I hadn’t envisioned it in that manner. I will probably have to calculate an average cost across the contracts. So basically if I create the Month/Year column in my Sales Table, I just have to use the treatas on the sales order numbers and the cost will automatically associate the correct location?

I was thinking I would need a double treatas function. Thank you for your help.

11

I marked as solution and sent a follow-up. I haven’t been able to apply the methodology to my model yet, but I am hoping it will work.

12

Hi @ibesmond ,

What I found a bit unusual in the sample data is that the “Invoice-nr-Costs” appears in both tables, in the Sales table, as well as in the Transaction Cost table. (see also screenshots below)
If the real data contains in both tables “Cost invoice numbers”, the provided Treatas-formula should work.

image

image