Forum Discussion

powerbiuser9999's avatar
powerbiuser9999
Icon for Helper I rankHelper I
5 years ago
Solved

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...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
5 years ago

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's avatar
powerbiuser9999
Icon for Helper I rankHelper I
5 years ago

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's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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