cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## 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!

1 ACCEPTED SOLUTION
Solution Sage

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

4 REPLIES 4
Solution Sage

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

Helper III

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

Solution Sage

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

Helper III

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!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors