Forum Discussion

smbolster's avatar
smbolster
Frequent Visitor
8 years ago
Solved

Problem With Calculate Measure

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?

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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] )
)

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      smbolster
      Frequent Visitor
      8 years ago
      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