Date Table Reference to multiple payment amounts and payment dates

[forti4040]

Hello All,

I have a file where I've linked DimDate[Date] to Projects[Ship Date] with an active relationship. For each project that has a ship date it also has three payment amounts and payment dates (Example: Projects[Payment1 Date], Projects[Payment1 Amount]) I'd like to create a graph showing the distribution of these payments by month (summing where any overlap). The issue is that the active relationship causes all of the dates to reflect the Ship Date, not the three different payment dates. 

Does anyone know how I would take data like what is shown below (quick snapshot from excel for example)

And graph it like what is shown below (different data than above but for reference only). The stacked bars would reflect Payments 1 / 2 / 3 where they may overlap in any given month/year. 

Any help would be appreciated!

[AkhilAshok · Accepted solution]

USERRELATIONSHIP function is your best friend here. You can enable an Inactive relationships inside a measure using this function:

Check here for more info.

[forti4040]

@AkhilAshok, 

Thank you for the response. I agree it sounds like USERRELATIONSHIP is likely how I would approach solving my problem. I just can't seem to get it to work. I've created a measure for each payment date exactly like what is shown in the link you provided and am unable to get results related to the data as intended. My charted data still relates to the active relationship dates...

Below is one of the measures I created as an example: 

PaymentOne =
          CALCULATE (
                    SUM ( Projects[FirstAmount] ),
                    USERELATIONSHIP (
          Projects[FirstPaymentDate],
          DIMDate[Date]
)
)

My active relationship is between Projects(STT) and DIMDate[Date]

If I'm trying to graph the sum of expenses across months as shown in my original post wouldn't I need to use calculated columns vs. measures?

I know just enough about Power BI to ask these questions but clearly not enough to be well versed in this!

Regards,

forti4040

[AkhilAshok]

Can you try to replicate the issue with a sample data where it doesn't work and provide that here?

[forti4040]

Well...that was frustrating...I created a sample to post and it worked!

So after digging for a while I found that my original query had my additional date columns set to Date/Time, not Date. Once I changed those all the info started pulling correctly. Such a small thing but such a big deal...

Thanks for the help @AkhilAshok!