Forum Discussion

That_Analyst's avatar
That_Analyst
Microsoft Employee
3 years ago

Count blanks

I want to get the count of Customers from June that were not seen in July.

So Count of customers that had sales in June but not in July. 

=DISTINCTCOUNT(CustomerId_June),CustomerId_July=blank()

When I use the above formula, CustomerId 1 and 2 are returned instead of just CustomerId 2. 

If a customer had sales in July I do not want to include them, I only want those who had sales only in June.

I've also tried adding a flag if(CustomerId_July=blnak(),1,0) but I still did not get the desired results.

 

Need Help

1 Reply

  • veenashenolikar's avatar
    veenashenolikar
    Helper V
    3 years ago

    Hi That_Analyst ,

     

    You could try creating a column with -

    Flag = VAR firstid =
        FIRSTNONBLANK ( 'Table'[ID_June], "" )
    VAR compare =
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALL ( 'Table' ), 'Table'[ID_July] = firstid)
        )
    RETURN
        IF ( ISBLANK ( compare ), 0, compare )
     
    Then create a measure for count as -
    Count = CALCULATE(DISTINCTCOUNT('Table'[ID_June]),FILTER('Table',[Flag]=1))
     
    Hope this helps!
     
    Regards,
    Veena.