Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Try this
Months In Selection New = CALCULATE ( COUNTROWS ( VALUES ( 'Real Dates'[Date Accounting Period] ) ), FILTER ( ALL ( 'Real Dates' ), 'Real Dates'[Date] <= [Max Accounting Period] ) )
Try this
Months In Selection New = CALCULATE ( COUNTROWS ( VALUES ( 'Real Dates'[Date Accounting Period] ) ), FILTER ( ALL ( 'Real Dates' ), 'Real Dates'[Date] <= [Max Accounting Period] ) )
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
User | Count |
---|---|
88 | |
72 | |
68 | |
64 | |
54 |
User | Count |
---|---|
98 | |
91 | |
74 | |
68 | |
63 |