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

[btreg]

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!

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I

<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.

 

[btreg]

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.

 

[Eric W]

My apologies, I didn't notice what forum this is in. Hopefully someone else will step in.

 

[btreg]

My apologies, I didn't notice what forum this is in. Hopefully someone else will step in.

No problem Eric, and actually your response has taught me something I can use elsewhere, so thanks for that!