Filter a table by the aggregation of its self

[tsql_simon]

I am struggling to filter a table by the aggregation of its self

Basically I have one Table which has a list of all payments as well a "zero" payment for the special few who get there subscription for free. The issue I am having is when I am trying to get
who is a "Paid" subscriber on a (by slicer) specific date. I have managed to get a cumulative total of Income and thought I had the count right till I realised I was not taking into account someone who may has a refund.

So from my table I would like to get the number of paid VIP subscribers on day X and for the same period the Year before

CompanyPayDatePayAmountIs VIPIs Zero PayerFinancial Year

AA01	15/12/2019	100	1	0	2019
AA02	12/12/2019	100	1	0	2019
AA02	14/12/2019	-100	1	0	2019
AA03	10/12/2019	100	1	0	2019
AA04	05/12/2019	45	0	0	2019
AA05	01/12/2019	0	1	1	2019
AA01	15/12/2018	100	1	0	2018
AA02	12/12/2018	100	1	0	2018
AA03	10/12/2018	100	1	0	2018
AA03	12/12/2018	-100	1	0	2018
AA03	14/12/2018	100	1	0	2018
AA04	05/12/2018	45	0	0	2018
AA05	01/12/2018	0	1	1	2018

So from my table by the 12th Dec I would expect a count of 2 FOR 2019; AA02 has Paid AA03 Has paid and AA05 Is also counted as he is a special "Is Zero Payer"
and 2 for 2018 AA02 and AA05 as AA03 was refunded

Then for the 16th Dec I would expect 3 for 2019; AA01,AA03 and AA05 and 4 for 2018 AA01,AA02,AA03(paid,refunded,paid again) and AA05

AA04 never gets counted as they are not a VIP

Now in SQL I can Do this easily BY Filtering the table by aggregated sub query of itself (For reference our Financial year starts 1 Nov)

SELECT COUNT(DISTINCT Company) FROM MyTable
WHERE Company NOT IN (SELECT Company FROM MyTable WHERE PayDate Between '20191101' and '20191212' GROUP BY Company HAVING SUM(PayAmount) >0 )
and PayDate Between '20191101' and '20191212'
and [Is Vip] = 1

[v-lid-msft · Accepted solution]

Hi @tsql_simon ,

We can create a measure to meet your requirement:

CountNumber =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER ( 'Table', [Is VIP] = 1 ),
            'Table'[Company],
            "Total", SUM ( 'Table'[PayAmount] ),
            "IsZero", MIN ( 'Table'[Is Zero Payer] )
        ),
        [Total] > 0
            || [IsZero] = 0
    )
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,

[tsql_simon]

That works perfectly, 3 days I did not think to aggregate the Boolean filed Doh! you are a God amongst us mere mortals your help is invaluable

Kindest Regards

Simon Richardson

[mwegener]

Hi @tsql_simon ,

check this out. SUMMARIZE your Data and FILTER on it.

Measure = COUNTROWS(FILTER(SUMMARIZE(MyTable,MyTable[Company],MyTable[Is VIP],"Sum of Payment",SUM(MyTable[PayAmount])),[Sum of Payment] > 0))

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.