Summing and selecting specific date

I have two tables. One is a table of invoice. There’s a [PAY] and [PAY TYPE]. I have another table that will sum up for the day. [Pay] is an amount, [Pay Type]= “credit”. I want to create a virtual row that sums up all the credits for the day.
select all with [pay type] = “credit”
sum up [Pay] if [Pay Type] = “credit” and [date] = today
Can somebody help with the expression?
My guess: sum(select([PAY TYPE] = CREDIT, [PAY], TRUE))

Well, you got the SUM(SELECT()) portion correct, but that SELECT expression is nowhere near properly formed, give this SELECT article another review:

You’ll also need this:

1 Like

Here’s my actual expression.
SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],AND([PAY TYPE] = “Credit”, Daily Report[APP DATE] = Form Responses 1[App Date], TRUE)))

I want to filter today’s report( Daily Report[App Date]) with my Datasheet(Form Responses 1[App Date])
It doesn’t work.

1 Like

For the criteria part, I’m trying to pull only data from Table 1 that has the same [date] from Table 2. I tried
SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],AND([PAY TYPE] = “Credit”, Daily Report[APP DATE] = Form Responses 1[App Date], TRUE)))

Daily Report[APP DATE] = Form Responses 1[App Date] → I’m trying to filter the date from one table with another. It’s valid but not summing up. Is my first expression wrong?

Ahh… I got it to work with this.

SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],and([PAY TYPE] = “Credit”,[app date] = today()), FALSE))

However, what can I use instead of [app date] = today() ? What if I want to choose another day besides today?

I assume you’re running this expression from the context of a record in your Daily Report Table? Try replacing TODAY() with [_THISROW].[APP DATE]

2 Likes