Using a variable date in a CALCULATE function

[powerbiuser9999]

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.

[VahidDM]

Hi @powerbiuser9999 

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  !!

[powerbiuser9999]

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.

[V-lianl-msft · Accepted solution]

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