SUMMARIZE with COUNT column

[ladyy27]

Hey guys, I'm a complete newbie in Power BI so I'll really appreciate your help.

I have a query of registrations:

REGISTRATION-ID	PERIOD	ENROLLMENT-DATE	PAYMENT-DATE	PAYMENT-STATUS	PAYMENT-DAY
2591453	OCT/2019 - FEB/2020	6/1/19	6/1/19	Paid	-2
2591454	OCT/2019 - FEB/2020	6/1/19	6/1/19	Paid	-2
2701697	OCT/2019 - FEB/2020	6/2/19	6/2/19	Paid	-1
3199960	OCT/2019 - FEB/2020	6/3/19		Not Paid
3202534	OCT/2020 - FEB/2021	6/1/20	6/1/20	Paid	-2
3202535	OCT/2020 - FEB/2021	6/2/20	6/2/20	Paid	-1
3202502	OCT/2020 - FEB/2021	6/2/20	6/2/20	Paid	-1
3203150	OCT/2020 - FEB/2021	6/3/20		Not Paid

"PAYMENT-DAY" is a calculated column that contains the number of days that have passed from the beginning of registrations in each period in countdown format where the last day of registrations in that period is the zero-day. This DAX formula is working fine.

I need to get a table visualization which includes:

• The period name (more than 1)
• Payment day
• Number of paid registrations per payment day
• Cumulative number of paid registrations (differenced by period)

Like this:

PERIOD	PAYMENT-DAY	Number-of-paid-registrations-per-payment-day	Cumulative-number-of-paid-registrations
OCT/2019 - FEB/2020	-2	2	2
OCT/2019 - FEB/2020	-1	1	3
OCT/2020 - FEB/2021	-2	1	1
OCT/2020 - FEB/2021	-1	2	3

In order to this, I created an aggregated table with SUMMARIZE:

CUMULATIVE-PAYMENTS = SUMMARIZE(
'REQUESTS','REQUESTS'[PERIOD],
'REQUESTS'[PAYMENT-DAY],
"Number-of-paid-registrations-per-payment-day",COUNTA('REQUESTS'[PAYMENT-STATUS])
)

After, I added a measure to this new aggregated table for the Cumulative number of paid registrations with this DAX formula:

Cumulative-number-of-paid-registrations = CALCULATE(
SUM(
'CUMULATIVE-PAYMENTS'[Number-of-paid-registrations-per-payment-day]),
FILTER(
ALLEXCEPT('CUMULATIVE-PAYMENTS','CUMULATIVE-PAYMENTS'[PERIOD]),
'CUMULATIVE-PAYMENTS'[PAYMENT-DAY] <= MAX('CUMULATIVE-PAYMENTS'[PAYMENT-DAY])
)
)

But the results aren't correct, the table visualization shows what I assume like "Payment Days" that don't have paid registrations (Not Paid). Consequently, it shows the wrong "Number of paid registrations" and wrong "Cumulative number of paid registrations".

Any suggestions or alternatives to solve this problem?

Thanks!

[amitchandak]

@ladyy27 , Try like one of the two

Cumulative-number-of-paid-registrations = CALCULATE(
SUM(
'CUMULATIVE-PAYMENTS'[Number-of-paid-registrations-per-payment-day]),
FILTER(
ALL('CUMULATIVE-PAYMENTS'),
'CUMULATIVE-PAYMENTS'[PAYMENT-DAY] <= MAX('CUMULATIVE-PAYMENTS'[PAYMENT-DAY])
&& 'CUMULATIVE-PAYMENTS'[PERIOD] = max('CUMULATIVE-PAYMENTS'[PERIOD]))
)

/////or 
Cumulative-number-of-paid-registrations = CALCULATE(
SUM(
'CUMULATIVE-PAYMENTS'[Number-of-paid-registrations-per-payment-day]),
FILTER(
ALLSELECTED('CUMULATIVE-PAYMENTS'),
'CUMULATIVE-PAYMENTS'[PAYMENT-DAY] <= MAX('CUMULATIVE-PAYMENTS'[PAYMENT-DAY])
)
)

[Greg_Deckler]

@ladyy27 - First welcome. Thanks for posting what you desire as output, very important. But, to understand what is going on, would need to understand your source data. Posting it as text in a table is extremely helpful. 

Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.