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.
Good morning,
I have a timeline/slicer; and seeking to sum the "Invoice Amount" based on this criteria:
If the date selected is "5/30/2018", then....
This will provide me a dynamic revolving accounts receivable balance calculation. The cells in yellow is what I'm needing calculated based on the criteria. Any help would be greatly appreciated. I have tried the "inactive relationship / userrelationship" techniques which didn't work and then I tried multiple date tables with calculate but couldn't get it to work.
Thank you & kindly - James
Solved! Go to Solution.
I didn't get no love on this response, but after 3 hours, I figured it out. Although not most optimal solution and even performance hungry, it works for now. If anyone can find a better solution, I would be very interested. Thank you!
Total Invoice Ex-Discount By TransDate Before Date Selected = /// This is a basic cumulative total pattern
CALCULATE (
SUM ('A/R Data'[Invoice Ex Discount] ),
FILTER (
all('Transaction Date'),
'Transaction Date'[Transaction Date] <= MAX('Transaction Date'[Transaction Date])))
Total Invoice Ex-Discount by PayDate before Date Selected = ///Allows slicer filter to apply while using another relationship
CALCULATE (
SUM ('A/R Data'[Invoice Ex Discount] ),USERELATIONSHIP('Payment Date'[Payment Date],'A/R Data'[Payment Date]),
FILTER (
all('Transaction Date'),'Transaction Date'[Transaction Date]<=[Date Select]),FILTER('Payment Date','Payment Date'[Payment Date]<=[Date Select]))
I didn't get no love on this response, but after 3 hours, I figured it out. Although not most optimal solution and even performance hungry, it works for now. If anyone can find a better solution, I would be very interested. Thank you!
Total Invoice Ex-Discount By TransDate Before Date Selected = /// This is a basic cumulative total pattern
CALCULATE (
SUM ('A/R Data'[Invoice Ex Discount] ),
FILTER (
all('Transaction Date'),
'Transaction Date'[Transaction Date] <= MAX('Transaction Date'[Transaction Date])))
Total Invoice Ex-Discount by PayDate before Date Selected = ///Allows slicer filter to apply while using another relationship
CALCULATE (
SUM ('A/R Data'[Invoice Ex Discount] ),USERELATIONSHIP('Payment Date'[Payment Date],'A/R Data'[Payment Date]),
FILTER (
all('Transaction Date'),'Transaction Date'[Transaction Date]<=[Date Select]),FILTER('Payment Date','Payment Date'[Payment Date]<=[Date Select]))
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 |
---|---|
111 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |