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

[btreg]

Here is a dummy dataset to illustrate my question:

Client_Ref	Start_of_Payment Month	Payment_Amount
101	01/01/2018	20
101	01/02/2018	20
101	01/03/2018	25
102	01/01/2018	15
102	01/02/2018	25
102	01/03/2018	10

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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:

	No of Clients with Payment Higher than Previous Month	No of Clients with Payment Lower than Previous Month	No of Clients with Payment Same as Previous Month
01/02/2018	1		1
01/03/2018	1	1

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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
1	Client_Ref	Start_of_Payment Month				No of Clients with Payment Higher than Previous Month	No of Clients with Payment Lower than Previous Month	No of Clients with Payment Same as Previous Month
2	101	1/1/2018	20		2/1/2018	1	0	1
3	101	2/1/2018	20		3/1/2018	1	1	0
4	101	3/1/2018	25
5	102	1/1/2018	15
6	102	2/1/2018	25
7	102	3/1/2018	10

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula F2 {=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)))} G2 {=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)))} H2 {=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)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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!