Problem With Calculate Measure

[smbolster]

I'm having an issue with max date in a calulate measure that seems like it should work.

The measure I'm starting with is as follows:

Months In Selection= CALCULATE(COUNTROWS(VALUES('Real Dates'[Date Accounting Period])),'Real Dates'[Date] <= 201801)

This works just fine.

As i start working through this I created 3 measures to work up to in order to replace the 201801 with dynamic data. They are as follows:

Max Accounting Date Month = MONTH(MAX(Bookings[Accounting Period Date])) 

  - returns 2018

Max Accounting Date Year = Year(MAX(Bookings[Accounting Period Date])) 

   - returns 1

Max Accounting Period = [Max Accounting Date Year]*100 + [Max Accounting Date Month] 

 - returns 201801

Then when i do the replacement I end up with:

Months In Selection New = CALCULATE(COUNTROWS(VALUES('Real Dates'[Date Accounting Period])),'Real Dates'[Date] <= Value([Max Accounting Period]) )

or

Months In Selection New = CALCULATE(COUNTROWS(VALUES('Real Dates'[Date Accounting Period])),'Real Dates'[Date] <= [Max Accounting Period] )

I tried both. This returns the same error:

   - A function 'Calculate' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I'm not sure how to fix this. Any ideas?

[Zubair_Muhammad · Accepted solution]

@smbolster

Try this

Months In Selection New =
CALCULATE (
    COUNTROWS ( VALUES ( 'Real Dates'[Date Accounting Period] ) ),
    FILTER ( ALL ( 'Real Dates' ), 'Real Dates'[Date] <= [Max Accounting Period] )
)

[smbolster]

Months In Selection New = CALCULATE ( COUNTROWS ( VALUES ( 'Real Dates'[Date Accounting Period] ) ), FILTER (VALUES ( 'Real Dates'[Date Accounting Period] ), 'Real Dates'[Date Accounting Period] <= [Max Accounting Period] ) )

That helped steer me, this is what i used, which allowed filters on the screen to work, like if i pick 2017 it returns 12.

Thanks