To generate report of payment received from one date to another date

I am working on a software for clinic. I have a Table which takes entry of my patients “date” wise. I have another column “Payment Received”.
I want to generate report of sum of payment received from “one particular date” to " another particular date". How to do it. Please help.

You could try something like this:
SUM(SELECT(payment received[amount] , AND( [date] < “xx/yy/zzzz” , [date] > “xx/yy/zzzz”)))

What I am doing wrong?

I see you’re setting a row filter condition. Not sure about your strategy here, but if you’re trying to create a slice with only the records in certain dates, then forget about the SUM(SELECT()) parts, just use the logical AND().

It is _THISROW, not _THISRAW.
Also, _thisrow is not an expression in itself, it is part of a deref, as in [_THISROW].[column].
And it should not be in quotation marks.
And you can’t really use it in a slice’s row filter condition.

Are you trying to compare the date to other date columns, or to a specific hard-coded date?

Here’s an example for filtering rows in a slice by date.
AND([date]< "02/29/2020” , [date]>"02/21/2020”)

Maybe you have a 2nd table, called Reports. In that table you have two columns, Date Start, Date End. If you want to sum the payments between those dates, create a virtual column with this expression.

SUM(SELECT(first table[payments received] , AND([date]< [_THISROW].[Date End] , [date]>[_THISROW].[Date Start])))

1 Like

Got it… Thanks for your valuable help

I have single table named “All Patients”
Inside that I have one column names [Date] which contains date wise entries of all new walk-in patient.

I have second column name [Payment Received] which contains amounts collected from particular patient…

Now I want to see sum of particular date range in dynamic mode in details view… E.g from 1 February to 15th February…

I am stuck here. I don’t get idea how to do

SUM of payment received

How does the app know what the start and end date are for the range?

That is where I stuck… How to make it possible… In my previous software I was able to see date range wise payments

Are you saying that you want users to be able to enter a starting and ending date for the report?
You then want to display the records between those two dates?

Are you trying to create a workflow report?
If not, can you explain how you want the report to be created?

1 Like

Yes… For example I want to display total collection from 01/02/2020 to 01/31/2020.

Also my accountant want to download data between the date range to maintain 3C form. Or automatically on first of every month, workflow send email (xls) file of last month’s records.

Here I have only one column of [Date]

You can make a workflow->report to send monthly. In it you can SUM up all payments from the previous month with the following expression:

SUM(
     SELECT(  table[payments received] , 
                  AND([date] <=  EOMONTH( TODAY() , -1)  ,
                      [date] >=  EOMONTH( TODAY() , -2) + 1   
                     )
            )
    )
1 Like

Thanks… Will try and update you.

this error occured



That field is the wrong spot to enter that formula. That field is for determining whether to fire the workflow. Please read up on workflows with the above articles. You’ll possibly want to enter that formula in an Email Body Template, and wrap the whole thing in << and >>, as shown in this screenshot.

1 Like

Got it thoroughly… Thanks