sum of amount up to selected date

[alec1982]

hi,

I have the following table

Global Date	Date Booked	JC Date Booked	Amount
8/30/2016	8/30/2016
8/31/2016	8/31/2016	8/31/2016	166726
9/4/2016	9/4/2016
11/28/2016
11/30/2016	11/30/2016	11/30/2016	5814.65
12/1/2016	12/1/2016

the user will select a date from the global date and lets say 11/28/2016.. I want to sum of amount where JC Date Booked is less than the selected global date. in this case it will return 166726.

if the user select 12/1/2016 then the sum of amount it will be 166726 + 5814.65.

I tried sum({$<[Global Date]= , [Date Booked]=, [JC Date Booked]={"<=$(=max([Global Date]))"} >}[Amount]) but that didnt give e what i want..

any idea on how to handle it..

Thanks..

Alec

[sunny_talwar]

May be you need to add the Date() function

Sum({$<[Global Date]= , [Date Booked]=, [JC Date Booked]={"<=$(=Date(Max([Global Date]), 'M/D/YYYY'))"} >}[Amount])

Also look here:

Dates in Set Analysis

[alec1982]

I think the format is fine as if I select a global date 11/30/2016 then I am getting the value of 5814.65. meaning it selects the available value but not all values up to that date.

[sunny_talwar]

Would you be able to share a sample app to take a look at?

[Anonymous]

Hi

try this !!?

Sum({$<[Global Date]= , [Date Booked]=, [JC Date Booked]={">=$(=Date(Min([Global Date]),'M/D/YYYY'))<=$(=Date(Max([Global Date]), 'M/D/YYYY'))"} >}[Amount])

[Anonymous]

or this  ??

Aggr(RANGESUM(ABOVE(SUM((Amount)),0,RowNo(TOTAL))),[Global Date])

[alec1982]

none worked.. I think the issue is because the columns are not located on the same table within the data model.. but is associated correctly though.. the table exported above is from the app where I just created a table box with the columns..

I am not able to share the app as it has sensitive data..

Any other thoughts.

[sunny_talwar]

Sensitivity can be addressed with this:

Preparing examples for Upload - Reduction and Data Scrambling

[alec1982]

thxs for the info.. the app has about 30 tables...

[alec1982 · Accepted solution]

Found the issue.. it is a month filter I am filtering on and not the global date.. so I added the global month filter to the expression and it works now..

thxs for the help..