cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tlenzmeier Regular Visitor
Regular Visitor

Relationship Problems

I have a report I am working on where I cannot get my dates to play nice with one another. The issue has to do with owner invoices and payments. My model has the customer payments PK linked to the owner invoice table. The owner invoice table is linked to my calendar table. I am unable to create an active relationship between the payment date and my calendar table. The result is that while my invoice amounts are correct, the payment amounts are not. If you look at Page 1, you will see the correct payment amounts for CompanyCode 002. I have a vague recollection that a bridge table might be the answer, but I'm not sure. Any help would be greatly appreciated.

 

https://adolfsonpeterson-my.sharepoint.com/:u:/g/personal/tlenzmeier_a-p_com/ERIlkpfK73BGi6PY29vc96o...

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Relationship Problems

Hi @tlenzmeier,

 

If i'm understanding your question correctly you want to have the calculation of payments based on calendar table, but you already have a date relationship between both tables.

 

Make an inactive relationship between the tables Calendar and Owner Payments (assuming this is were you have the payment information) then just change your payment measure to:

 

Payment Amount =
CALCULATE (
    SUM ( 'Owner Payments'[PaymentAmount] );
    LEFT ( 'Owner Payments'[Transaction Code]; 1 ) = "P";
    USERELATIONSHIP ( 'Calendar'[Date]; 'Owner Payments'[Payment Date] )
)

The last part is activating the relationship you created and making the calculations based on context final result below:

 

 

AR.png

 

Attach PBIX file.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Super User
Super User

Re: Relationship Problems

Hi @tlenzmeier,

 

If i'm understanding your question correctly you want to have the calculation of payments based on calendar table, but you already have a date relationship between both tables.

 

Make an inactive relationship between the tables Calendar and Owner Payments (assuming this is were you have the payment information) then just change your payment measure to:

 

Payment Amount =
CALCULATE (
    SUM ( 'Owner Payments'[PaymentAmount] );
    LEFT ( 'Owner Payments'[Transaction Code]; 1 ) = "P";
    USERELATIONSHIP ( 'Calendar'[Date]; 'Owner Payments'[Payment Date] )
)

The last part is activating the relationship you created and making the calculations based on context final result below:

 

 

AR.png

 

Attach PBIX file.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

tlenzmeier Regular Visitor
Regular Visitor

Re: Relationship Problems

So simple! Thank you!!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 3,190 guests
Please welcome our newest community members: