Visual using USERELATIONSHIP

Question

Hi all,&nbsp;I'm trying to create a simple column chart as follow&nbsp;with 2 tables: Calendar Date and Contract table.I have 2 date relationships between both tables, by using Date column in Calendar Date table&nbsp; with one active relationship with Contract Active Date and another inactive one with Contract End Date, from Contract table.For this visual, I need to use Contract End Date in the DAX formula.And I'd like to use Month column from Calendar Date, for easier interaction with other visuals.But it's not as expected.&nbsp;When I'm using Month from Contract End Date, the visual is correct though.This is the DAX I used. I'm not sure which one is wrong, and hope to get help.Value of Contract Expected to be Ended = var _CurrentProdDate =&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; MAXX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Production,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Production[Date]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; ALL( 'Calendar Date'[Date] ),&nbsp; &nbsp; &nbsp; &nbsp; Production[Status]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = "Being Processed"&nbsp; &nbsp; )var _ContractToBeEnded =&nbsp; &nbsp; SUMX (&nbsp; &nbsp; &nbsp; &nbsp; VALUES ( Contract[Customer] ),&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX ( Contract[Annual Contract Value] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Calendar Date'[Date]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt;= _CurrentProdDate,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Contract[Contract End Date],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Calendar Date'[Date]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )return&nbsp; &nbsp; _ContractToBeEnded&nbsp;Appreciate any help please.&nbsp;Regards,VVI

lbendlin · Answer

Please 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

vvi · Answer

Hi&nbsp;lbendlin&nbsp;&nbsp;Thanks for the reminder.Here's the data of source table.&nbsp;CustomerAnnual Contract ValueActive DateContract Start DateContract End DateCust A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40,00031-Dec-191-Mar-1828-Feb-23Cust A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50,00031-Dec-201-Mar-1828-Feb-23Cust A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 55,00031-Dec-211-Mar-1828-Feb-23Cust A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60,00031-Dec-221-Mar-1828-Feb-23Cust A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75,00028-Feb-231-Mar-1828-Feb-23Cust B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75,00031-Dec-191-Jun-1931-May-22Cust B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75,00031-Dec-201-Jun-1931-May-22Cust B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50,00031-Dec-211-Jun-1931-May-22Cust B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50,00031-May-221-Jun-1931-May-22Cust C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,00031-Dec-211-May-2130-Apr-23Cust C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,00031-Dec-221-May-2130-Apr-23Cust C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,00030-Apr-231-May-2130-Apr-23Cust D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,00031-Dec-221-Sep-2231-Aug-24Cust D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50,00031-Dec-231-Sep-2231-Aug-24Cust D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50,00031-Aug-241-Sep-2231-Aug-24&nbsp;And the result expected in table view&nbsp;MonthValue of Contract Expected to be Ended2023-02750002023-04150002024-0850000

lbendlin · Answer

What is the importance of Contract Start Date?&nbsp; Is it part of the issue?
&nbsp;
how are you amortizing the annual contract value over the months?&nbsp; Or is that not important?
&nbsp;
Is this what you are looking for?&nbsp;

&nbsp;
&nbsp;

vvi · Answer

Hi&nbsp;lbendlin&nbsp;&nbsp;Contract Start Date is not the issue now, and there's no need to amortize contract over the year.&nbsp;Is your visual using the period from Calendar Table?

vvi · Answer

Hi&nbsp;lbendlin&nbsp;&nbsp;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:&nbsp;'Calendar Date'[Date]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt;=&nbsp;_CurrentProdDateorContract[Contract End Date]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt;= _CurrentProdDate&nbsp;But both doesn't work.The later still shows 2022 value, which I don't need.

Forum Discussion

Visual using USERELATIONSHIP

6 Replies

Related content

USERELATIONSHIP() - Visual Filter

RLS and USERELATIONSHIP problem

USERELATIONSHIP problems with n:m-relationship

Where to use Userelationship

conditional formatting lost when USERELATIONSHIP ist used

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

Month	Value of Contract Expected to be Ended
2023-02	75000
2023-04	15000
2024-08	50000