Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
forti4040
Helper III
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)

 

Untitled.png

 

 

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. 

 

Untitled2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any help would be appreciated!

1 ACCEPTED SOLUTION
AkhilAshok
Solution Sage
Solution Sage

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

 

Check here for more info.

View solution in original post

4 REPLIES 4
AkhilAshok
Solution Sage
Solution Sage

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

 

Check here for more info.

@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

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

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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