Forum Discussion
Using a variable date in a CALCULATE function
Hi
I wonder if someone can help me, I have a project that involves using a CALCULATE function, and adding some filter values at the end of the function. One of these needs to a date value which is based on SELECTEDVALUE of a slicer.
My slicer uses the year part of a date field, and it is this I need to modify.
My function is:
CALCULATE(DISTINCTCOUNT(column),Column = 1,Date>DATE(SELECTEDVALUE(Datefield.Year)-1,4,1)))
So, for example if the user has selected '2021' from the year slicer, I am looking for this function to use '2020' as its filter.
This is being thrown back at me with the error that a true/false value is not allowed with CALCULATE.
I've tried adding the calculation as a variable at the start, but again it fails.
Is there something obvious I'm missing? Any help would be appreciated.
Please try to create a measure like below:
Measure 2 = CALCULATE(DISTINCTCOUNT('Table (2)'[Column]),FILTER(ALL('Table (2)'),'Table (2)'[Column1]=1&&YEAR('Table (2)'[date])=SELECTEDVALUE('Table (2)'[date].[Year])-1))For more details,please refer to this sample .pbix
3 Replies
Try to use FILTER dax code, like this:
measure = CALCULATE(DISTINCTCOUNT(column),Column=1,filter(table,Date>DATE(SELECTEDVALUE(Datefield.Year)-1,4,1)))
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos !!
Hi VahidDM
Thanks for this, however it does not give me the result I am looking for.
If it helps, the following DAX does work.
CALCULATE(DISTINCTCOUNT(Column),Column=1,Date>DATE(2020,4,1)))
(I've hard coded a date for the test to work)
Its probably worth noting that:
DATE(SELECTEDVALUE(Column.Year)-1,4,1) works on its own, but not as part of the CALCULATE function.Please try to create a measure like below:
Measure 2 = CALCULATE(DISTINCTCOUNT('Table (2)'[Column]),FILTER(ALL('Table (2)'),'Table (2)'[Column1]=1&&YEAR('Table (2)'[date])=SELECTEDVALUE('Table (2)'[date].[Year])-1))For more details,please refer to this sample .pbix
