Measure to count number of clients making higher payment than previous month

B

btreg

New Member
Joined
Feb 29, 2016
Messages
30
Here is a dummy dataset to illustrate my question:

[TABLE="class: grid, width: 391"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Client_Ref[/TD]
[TD]Start_of_Payment Month[/TD]
[TD]Payment_Amount[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

I am wondering if it is possible to write measures that would enable me to count how many clients in one month have paid more / less / the same compared to the previous month.

The resulting pivot from the above would look as follows:

[TABLE="class: grid, width: 515"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]No of Clients with Payment Higher than Previous Month[/TD]
[TD]No of Clients with Payment Lower than Previous Month[/TD]
[TD]No of Clients with Payment Same as Previous Month[/TD]
[/TR]
[TR]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

If anyone could advise how to write the measure for the "higher" example, this would be much appreciated.

(In this example, each client makes just one payment per month. I would also like to understand how the measure would need to be adapted if clients made several payments per month i.e. if I need to compare the sum of payments in two months).

Thank you in advance!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Try:

ABCDEFGHI

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Client_Ref[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Start_of_Payment Month[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]No of Clients with Payment Higher than Previous Month[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]No of Clients with Payment Lower than Previous Month[/TD]
[TD="align: right"]No of Clients with Payment Same as Previous Month[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]101[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]101[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "] 2/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]101[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]102[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]102[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]102[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=SUM(IF(IFERROR(<fo
MATCH($A$2:$A$10,$A$2:$A$10,0),-1</fo
)=ROW($A$2:$A$10)-ROW($A$2)+1,IF(SUMIFS($C$2:$C$10,$A$2:$A$10,$A$2:$A$10,$B$2:$B$10,">"&EOMONTH(E2,-2),$B$2:$B$10,"<="&EOMONTH(E2,-1))< SUMIFS($C$2:$C$10,$A$2:$A$10,$A$2:$A$10,$B$2:$B$10,">"&EOMONTH(E2,-1),$B$2:$B$10,"<="&EOMONTH(E2,0)),1)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=SUM(IF(IFERROR(MATCH($A$2:$A$10,$A$2:$A$10,0),-1)=ROW($A$2:$A$10)-ROW($A$2)+1,IF(SUMIFS($C$2:$C$10,$A$2:$A$10,$A$2:$A$10,$B$2:$B$10,">"&EOMONTH(E2,-2),$B$2:$B$10,"<="&EOMONTH(E2,-1))>SUMIFS($C$2:$C$10,$A$2:$A$10,$A$2:$A$10,$B$2:$B$10,">"&EOMONTH(E2,-1),$B$2:$B$10,"<="&EOMONTH(E2,0)),1)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=SUM(IF(IFERROR(MATCH($A$2:$A$10,$A$2:$A$10,0),-1)=ROW($A$2:$A$10)-ROW($A$2)+1,IF(SUMIFS($C$2:$C$10,$A$2:$A$10,$A$2:$A$10,$B$2:$B$10,">"&EOMONTH(E2,-2),$B$2:$B$10,"<="&EOMONTH(E2,-1))=SUMIFS($C$2:$C$10,$A$2:$A$10,$A$2:$A$10,$B$2:$B$10,">"&EOMONTH(E2,-1),$B$2:$B$10,"<="&EOMONTH(E2,0)),1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I had to change the date formats for my regional settings, you shouldn't have to do that. The only difference in the 3 formulas is the operator. This should also work for multiple payments per month.
 
Upvote 0
Hi Eric, thank you for your response, though I am hoping to create DAX measures to perform the calculations as I want to be able to use these measures in Pivot Tables created with PowerPivot.
 
Upvote 0
My apologies, I didn't notice what forum this is in. Hopefully someone else will step in.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Keeping track of donations and member payments
Replies
2
Views
398
Silencer
S
A
  • Question Question
Measure to count absences by calendar month, based on start date and end date range
Replies
1
Views
2K
Matt Allington
M
H
  • Question Question
How to count Unique Client ID by month and year
Replies
5
Views
402
Fluff
Fluff
L
  • Question Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
479
Lukma
L
john Smith72
  • Solved
count days between dates for each month by drop list
Replies
9
Views
380
Tetra201
Tetra201

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top