Using measure in a DAX filter does not work

[jmkvalsund]

Im trying to use a measure in a DAX filter to count the numbers of customers for each Key Account Manager (KAM) at a given date, but it does not seem to work.

My measure is calculated like this, and gives me the dateID for the end of the completed month which is two completed months ago (in january, last date of october previous year etc). M_MaxDateID is the latest dateID in Sales.

M_StartDate2MonthsInterval =
VAR SlicerDate = MAX('Sales'[DateID] )
RETURN CALCULATE(max('Sales'[DateID])
, Filter(ALL('Sales'[DateID]), IF(VALUE(MID([M_MaxDateID],5,2))<2,'Sales'[DateID] <= (SlicerDate-200),'Sales'[DateID] <= (SlicerDate-9000))))

M_MaxDateID = MAX('Sales'[DateID])

When I put these measure on a card, I see the values I want, i.e. today it's  20221031 for M_StartDate2MonthsInterval and 20230130 for M_MaxDateID.

But then I try to put M_StartDate2MonthsInterval in a Filter in another Measure it fails.

This measure is blank:
M_NumberOfCustomersTwoMonthsAgo = CALCULATE(DISTINCTCOUNT('Sales'[CustomerID]),'Sales'[KAM_email]="someone@mycompany.com",'Sales'[DateID] = [M_StartDate2MonthsInterval]))

If I instead put in the value I know M_StartDate2MonthsInterval holds (20221031), I get my number of customers of 20221031:
M_NumberOfCustomersTwoMonthsAgo = CALCULATE(DISTINCTCOUNT('Sales'[CustomerID]),'Sales'[KAM_email]="someone@mycompany.com",'Sales'[DateID] = 20221031))

Why can't I put the measure in a filter?

Regards,

John Martin

[johnt75 · Accepted solution]

Does it work if you store the result of the measure in a variable ?

M_NumberOfCustomersTwoMonthsAgo =
VAR ReferenceDate = [M_StartDate2MonthsInterval]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sales'[CustomerID] ),
        'Sales'[KAM_email] = "someone@mycompany.com",
        'Sales'[DateID] = ReferenceDate
    )

[jmkvalsund]

Thanks a lot, that was it!
I do know too little about the basic mechanism in Power BI, think I'll have to attend a class. Thanks again!

[jmkvalsund]

Yes, because if I assign M_STartDate2MonthsInterval to a card it shows 20221130, and if I then replace <'Sales'[DateID] = [M_StartDate2MonthsInterval]> with <'Sales'[DateID] = 20221130> I get 397 customers. So there are data, but something about the use of the measure in the filter screws things up.

[jmkvalsund]

I tried your solution, but it didn't change anytning. And as far as I can see, the only difference is that the FILTER statement is written explicitly instead of being implisitt in the CALCULATE statement. 
M_NumberOfCustomersTwoMonthsAgo is still (Blank) when using M_STartDate2MonthsInterval as value for criteria.

[johnt75]

Are you certain that you have sales on the exact date in question ?

[johnt75]

You can't use measures as filters because they are calculated at the aggregate level not the individual row level. To calculate at the row level you need to use an iterator to introduce a row context, so you need to use the FILTER function.

Try

M_NumberOfCustomersTwoMonthsAgo =
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[CustomerID] ),
    FILTER (
        'Sales',
        'Sales'[KAM_email] = "someone@mycompany.com"
            && 'Sales'[DateID] = [M_StartDate2MonthsInterval]
    )
)