Forum Discussion
Calculate Difference Between two date columns
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....
- sum everything before "5/30/2018" on 'Transaction Date'
- AND
- everything after "5/30/2018" on the 'Payment Date'.
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
Goal is to return value in red cell.
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]))
1 Reply
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]))