Forum Discussion
Related tables and forcasting
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!
4 Replies
- Jihwan_Kim
Super User
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
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.
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.
