Related tables and forcasting

[Anonymous]

Hello all,

I have 2 tables which are related in a Many to 1 by Contract name.   

Sales (Many)  = A list of sales to multiple customers with these important columns

Sales number	Contract name	Product	Price
1	Customer 1	Oranges	10
2	Customer 1	Apples	50
3	Customer 2	Oranges	2
4	Customer 1	Oranges	34
5	Customer 2	Oranges	56
6	Customer 1	Apples	78
7	Customer 1	Oranges	70
8	Customer 1	Apples	30
9	Customer 2	Apples	10

From that I have metrics that work out daily average sales both in volume and cost  (there's a date field which I don't think is important for my question)

ContractDate (to 1) = is a simple 3 column table like this

Contract Name	Date contract ends	Days until contract ends
Customer 1	31/05/2022	396
Customer 2	31/12/2021	245
Customer 3	14/09/2021	137
Customer 4	28/06/2021	59
Customer 5	22/05/2021	22

Each customer name only appears once in this list.

The outcome I want to get to is to be able to group by customer or product name across customers, take my daily average for that product and multiply it by the Days until contract ends value for that customer.  That will let me forecast how many sales and their value until the end of the contract.

The trouble I run into is getting each customer's Days until contract ends value into a metric where I can use it.

I hope that makes sense and thanks for any help!

[v-yangliu-msft]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Create calculated column.

Column = RELATED(Table2[Days until contract ends])

Column 2 = 'Table'[Price]*'Table'[Column]

2. Create calculated table.

Sales =
SUMMARIZE('Table','Table'[Contract Name],'Table'[Product],"sales",SUM('Table'[Column 2]))

3. Result:

Best Regards,

Liu Yang

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

[Jihwan_Kim]

Hi, @Anonymous 

I am not sure if I understood your question correctly, but please try the below whether it is what you are looking for.

Forecast sales =

SUMX(Sales, Sales[Price] * RELATED(ContractDate[Days until contract ends]))

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@Jihwan_Kim 

Yeah that's roughly how simple I thought it would be.  I thought it would be

Daily_Average * RELATED(ContractDate[Days until contract ends])

But the measures simply won't allow me to add the Days Until contract ends field in there which is why I am stuck.  I can use it in column calculations, but not measures.   Any ideas on what might be happening?

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I am not sure how your data model looks like, but please check the relationship between the two tables.

Or, if it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thanks.