Calculate sum before a specific date

[mrbqlik2009]

hi,

suppose i have this table in database.

Name	Date	amount
x	1/15/2020	20
x	1/20/2020	27
x	1/23/2020	15
y	2/1/2020	45
y	2/5/2020	31

i want calculate sum(amount) of records with name 'x' and before date for example 01/22/2020 in expression in a chart.

for example the answer become 20+27=47

show 47.

[Taoufiq_Zarra · Accepted solution]

Maye be :

=sum({$<Name={'x'},Date={"<=$(=Date#('1/22/2020','M/DD/YYYY'))"}>} amount)

[mrbqlik2009]

thank you for reply.

I put specific date in a variable. 

suppose Date and specific date is not in format 'MM/DD/YYYY'.

how can i calculate both in this format.

[Taoufiq_Zarra]

@mrbqlik2009 

you can change Date Format in 'MM/DD/YYYY' to another 

bellow with variable :

=sum({$<Name={'x'},Date={"<=$(=vDate)"}>} amount)

[mrbqlik2009]

can put Date#('01/01/2020','YYYY/MM/DD') in expression?

[Taoufiq_Zarra · Accepted solution]

no but

=Date#('2020/01/01','YYYY/MM/DD')

Date#