Find Customer count those not received amount

[MSuser5]

Hi All,

I have raw data which is contains customers monthly amount, In this i want to find how many customers received amount first quarter(Q1) and in those customers who haven't get amount in second quarter(need count of customers)

example source data:

Expected Result:

Kindly sugget how to create measure 1 and measure 2 as results.

Thanks,

MS

[danextian · Accepted solution]

Hi @MSuser5 ,

First off, when you post a sample, please post one that be easily copy-pasted (not an image). The community wants to help but make it easy for us to help you by not making us manually type the data. I had to use an OCR to avoid most of the manual stuff.

Going back, your data is not in the best format for reporting. I''d unpivot everything except Customer column in Power Qurery before creating measures. Change the data type accordingly after unpivoting. Load the data and then create the measures.  See below:

Customers with amount received in Q1 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER ( FILTER ( 'Table', QUARTER ( 'Table'[Date] ) = 1 ), 'Table'[Value] > 0 )
)

Customers with amount received in but not in Q2 = 
VAR q1 =
    //summary of customers that received in Q1
    SUMMARIZE (
        FILTER ( 'Table', NOT ( ISBLANK ( [Customers with amount received in Q1] ) ) ),
        'Table'[Customer]
    ) //summary of customers that received in Q2
VAR q2 =
    SUMMARIZE (
        FILTER ( FILTER ( 'Table', 'Table'[Value] > 0 ), QUARTER ( 'Table'[Date] ) = 2 ),
        'Table'[Customer]
    )
RETURN
    //removes the customer that are in q1 based on the summary in q2 and then counts the remaining ones
    COUNTROWS (
        EXCEPT ( q1, q2 )
    )

Please see sample pbix.

[MSuser5]

Hi @danextian ,

Thanks for the solution and guidance .. It's working fine!!

[MSuser5]

Hi All,

Any suggesstions / Solutions please

Thanks ,

MS