Forum Discussion
Date Table Reference to multiple payment amounts and payment dates
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!
USERRELATIONSHIP function is your best friend here. You can enable an Inactive relationships inside a measure using this function:
Check here for more info.
4 Replies
USERRELATIONSHIP function is your best friend here. You can enable an Inactive relationships inside a measure using this function:
Check here for more info.
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
Can you try to replicate the issue with a sample data where it doesn't work and provide that here?