Calculate Payment received where Billing Year is same as Payment Year

[gvanbuhler]

I am trying to create a column or measure where I can provide the amount paid where the Billing Year = the Payment Year.  I need it to be dynamic so that if we are looking at year 2019 we only see payments for billings done in 2019 or any other year.  I've already created a YTD payments but in January the billing was from December.  Any thoughts?

I have tables.columns:
Date Dim Billing.Billing Year

Date Dim Payment.Payment Year

Sales Transactions Header.Billing Date

Sales Transactions Allocation.Payment Date

[lbendlin]

This is similar to the "Count of opened and closed tickets per time period" pattern.  Usually this involves a date (calendar) table, an active relationship with one date ( for example billing date) and an inactive relationship with the other (payment date).  Then in you DAX you can use USERELATIONSHIP() to switch as needed.

[gvanbuhler]

I will research more on the  "Count of opened and closed tickets per time period" pattern.  I have two different date dim tables for the two dates, would it only work with using an inactive relationship and USERELATIONSHIP()?

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[gvanbuhler]

I'm having some issues putting the data in a usable format that is not getting an HTML Error

Billing Year     Payment Rec'd Year      Amount

   2019                     2019                       $75

   2019                     2020                       $100

   2020                     2020                       $200

   2020                     2021                       $250

   2021                     2021                       $300

So: 

2019 billings = $175

2019 payment rec'd = $75

2020 billing = $450

2020 payment rec'd = $300

2020 payment rec'd for same year billing = $200 ****this is the calculation I'm looking for.  I already have the YTD and correspodning measures complete.  just need to figure out how to add the filter (BillingYear = PaymentYear)

Transaction Amount ABS YTD Same Billing Year =

CALCULATE( [Sum of Transaction Amount ABS],

FILTER(

ALL('Date Dim (Transaction)'),

'Date Dim (Transaction)'[Transaction Year] = 'Date Dim (Billing)'[Current Billing Year]

))