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.
I'm having trouble creating a cumulative sum with both direct and indirect relationships in play. Simplified data structure is as shown - direct relationships between DateTable<-->Invoices and Invoices <--> Payments, then an inactive relationship between Payments <--> DateTable
I have a report level filter on InvoiceDate, to only show data for invoices issued within a certain period (in this case, year), then a matrix of payments per period. This uses the following measure, which works as intended:
Payments = CALCULATE(SUM([Payment Amount]),USERELATIONSHIP(DateTable[Date],Payments[PaymentDate]))
However, I can't figure out the cumulative total - I've tried the following. I've seen suggestions to nest CALCULATE statements, which I think the [Payments] measure is already doing, but I've also copied that calculation in place of the measure, to no effect.
Cumulative Payments = CALCULATE([Payments],FILTER(ALLSELECTED(DateTable),DateTable[Date]<=MAX(DateTable[Date])))
Cumulative Payments = CALCULATE([Payments],FILTER(ALLSELECTED(DateTable),DateTable[Date]<=MAX(DateTable[Date])),USERELATIONSHIP(DateTable[Date],Invoices[InvoiceDate]))
Cumulative Payments = CALCULATE(CALCULATE(SUM([Payment Amount]),USERELATIONSHIP(DateTable[Date],Payments[PaymentDate])),FILTER(ALLSELECTED(DateTable),DateTable[Date]<=MAX(DateTable[Date])),USERELATIONSHIP(DateTable[Date],Invoices[InvoiceDate]))
Any ideas gratefully received!
Solved! Go to Solution.
Hi,
I think by removing the FILTER in your first cumulative calculation there shouldn't be any issues. If there are try using CROSSFILTER to make sure there isn't any cyclic references.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Thanks for replying - without the filter, isn't it just the original measure without being cumulative?
I'm sure there will be a way around this through DAX, but I managed to solve it much more simply - created another table (DateTablePayment = 'DateTable'), removed the inactive relationship and added a new active relationship between Payments[PaymentDate] and DateTablePayment[Date]
Hi,
I think by removing the FILTER in your first cumulative calculation there shouldn't be any issues. If there are try using CROSSFILTER to make sure there isn't any cyclic references.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Thanks for replying - without the filter, isn't it just the original measure without being cumulative?
I'm sure there will be a way around this through DAX, but I managed to solve it much more simply - created another table (DateTablePayment = 'DateTable'), removed the inactive relationship and added a new active relationship between Payments[PaymentDate] and DateTablePayment[Date]
Hi @marine_adz
I meant removing the FILTER and its corresponding () signs. The basic pattern for RT calculation is like this:
var maxdate = MAX('Calendar'[Date])
CALCULATE(SUM(Table[Value]),ALL(Calendar),Calendar[Date]<=maxdate)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |