Forum Discussion
Visual using USERELATIONSHIP
Super UserPlease provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi lbendlin
Thanks for the reminder.
Here's the data of source table.
| Customer | Annual Contract Value | Active Date | Contract Start Date | Contract End Date |
| Cust A | 40,000 | 31-Dec-19 | 1-Mar-18 | 28-Feb-23 |
| Cust A | 50,000 | 31-Dec-20 | 1-Mar-18 | 28-Feb-23 |
| Cust A | 55,000 | 31-Dec-21 | 1-Mar-18 | 28-Feb-23 |
| Cust A | 60,000 | 31-Dec-22 | 1-Mar-18 | 28-Feb-23 |
| Cust A | 75,000 | 28-Feb-23 | 1-Mar-18 | 28-Feb-23 |
| Cust B | 75,000 | 31-Dec-19 | 1-Jun-19 | 31-May-22 |
| Cust B | 75,000 | 31-Dec-20 | 1-Jun-19 | 31-May-22 |
| Cust B | 50,000 | 31-Dec-21 | 1-Jun-19 | 31-May-22 |
| Cust B | 50,000 | 31-May-22 | 1-Jun-19 | 31-May-22 |
| Cust C | 10,000 | 31-Dec-21 | 1-May-21 | 30-Apr-23 |
| Cust C | 15,000 | 31-Dec-22 | 1-May-21 | 30-Apr-23 |
| Cust C | 15,000 | 30-Apr-23 | 1-May-21 | 30-Apr-23 |
| Cust D | 25,000 | 31-Dec-22 | 1-Sep-22 | 31-Aug-24 |
| Cust D | 50,000 | 31-Dec-23 | 1-Sep-22 | 31-Aug-24 |
| Cust D | 50,000 | 31-Aug-24 | 1-Sep-22 | 31-Aug-24 |
And the result expected in table view
| Month | Value of Contract Expected to be Ended |
| 2023-02 | 75000 |
| 2023-04 | 15000 |
| 2024-08 | 50000 |
- lbendlin
What is the importance of Contract Start Date? Is it part of the issue?
how are you amortizing the annual contract value over the months? Or is that not important?
Is this what you are looking for?
Hi lbendlin
Contract Start Date is not the issue now, and there's no need to amortize contract over the year.
Is your visual using the period from Calendar Table?
Hi lbendlin
As addition, I'd like to show the contract value for the year of current production year and moving forward. So, previous year, i.e. 2022 is not required.
So, that's why I'm adding a filter:
'Calendar Date'[Date]>= _CurrentProdDateorContract[Contract End Date]>= _CurrentProdDateBut both doesn't work.The later still shows 2022 value, which I don't need.