Forum Discussion
powerbiuser9999
Helper I
Helper IUsing 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...
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
powerbiuser9999Helper I
Helper IHi 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
Community Support
Community SupportPlease 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