DAX Measure - distinct count of YTD # of Customers with an condition on YTD revenue different than 0

[Anonymous]

Dear PowerBI experts,

after days spent on this forum and visited hundreds of other web resources I've come to the conclusion that I'm no longer able to sort out my issue by myself, hence badly need your support in my project. The case is: have to somehow build a measure that counts distinct # of Customers from YTD perpective with an additional condition that only those Cusotmers should be counted with revenue different than 0 in the same period of time (YTD as well).

So far I've made two steps so far which works like a charm:

Step1 = CALCULATE ( DISTINCTCOUNT ( FactTM1[Customer] ) ) - this one calculates all distinct Customers from the database

Step2 = CALCULATE ( [Step1], ALL ( DimCalendar ), DATESYTD ( DimCalendar[Date] ) ) - this one add YTD context to the Step1

Step3 = ??? - missing part, I have no clue how to add a condition here with sth like that: "calculate pool of customers from Step2 with a prerequisite of YTD revenue different than 0". Of course I've got a separate measure for YTD revenue:

YTD_USD = CALCULATE (SUM(FactTM1[Revenue]), DATESYTD ( DimCalendar[Date] ), ALL ( DimCalendar ))

so initially I thought of sth like this for Step3: = CALCULATE([Step2], YTD_USD<>0) but the PowerBI displays: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Got lost & stucked there, so desperately looking for any form of help here. Anyone? 🙂

[Ashish_Mathur]

Hi,

Try these measures

Revenue = sum(data[rev])

YTD revenue = calculate([revenue],datesytd(DimCalendar[Date],"31/12"))

Measure = countrows(filter(values(data[Customer code]),[YTD revenue]>0))

To your visual ensure that you drag Month/Year from the calendar visual.

Hope this helps.

[Anonymous]

Hi Ashish,

unfortunately your solution does not display correct values but still thank you for involvement here, appreciated! Glad that Pat's solution works!:)

[mahoneypat · Accepted solution]

FYI that you don't need the CALCULATE around your Step 1 measure.  Please try this expression to get your desired result.

Customers with Sales YTD =
CALCULATE (
    COUNTROWS (
        FILTER (
            VALUES ( FactTM1[Customer] ),
            CALCULATE (
                SUM ( FactTM1[Revenue] )
            ) > 0
        )
    ),
    ALL ( DimCalendar ),
    DATESYTD ( DimCalendar[Date] )
)

You may not need the ALL in there, so you can try w/o that too.

Regards,

Pat

[Anonymous]

Hi Pat,

jeeeeez, couldn't figure this out by myself so thanks a million, truly appreciate it! Works like a charm now! 🙂