Forum Discussion

T_von_Axt's avatar
T_von_Axt
Helper I
4 years ago
Solved

How do I use Date Slicers in Formulas?

Hello,   I am currently having issues with a specific use-case where I need to get both date values from a date slicer and then use it in a formula:   This slicer uses data from a single co...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    T_von_Axt , Try a measure like

     


    measure =
    var _max = maxx(allselected('Table'),'Table'[Date]) // Use Date(2021,01,31)
    var _min = eomonth(_max,-12) +1
    var _calc = calculate(count(Table[Document No]), filter(all('Table'), 'Table'[Date] >=_min && 'Table'[Date] <=_max))
    return
    if(isblank(_calc) && max('Table'[Date]) <_max , 0, _calc)

     

    But better have date table. Independent date table will do better

     


    measure =
    var _max = maxx(allselected('Date'),'Date'[Date]) // Use Date(2021,01,31)
    var _min = eomonth(_max,-12) +1
    var _calc = calculate(count(Table[Document No]), filter(('Table'), 'Table'[Date] >=_min && 'Table'[Date] <=_max))
    return
    if(isblank(_calc) && max('Table'[Date]) <_max , 0, _calc)

T_von_Axt's avatar
T_von_Axt
Helper I
4 years ago

Hi amitchandak,

Thank you for your answer.

I am currently trying to implement this, but Power BI keeps throwing an error: "The syntax for 'var' is incorrect. (DAX(var _max = maxx(allselected(GENERAL_DATA[DATE]),GENERAL_DATA[DATE] // Use Date(2021,01,31)var _min = eomonth(_max,-12) +1var _calc = calculate(count(GENERAL_DATA[DOCUMENT_NUMBER]), filter((GENERAL_DATA), GENERAL_DATA[DATE] >=_min && GENERAL_DATA[DATE] <=_max))returnif(isblank(_calc) && max(GENERAL_DATA[DATE]) <_max , 0, _calc)))."

 

Do you know what am I doing wrong here?

 

Edit: It was just a missing parenthesis.

amitchandak's avatar
amitchandak
Super User
4 years ago

T_von_Axt , This would be like

 

var _max = maxx(allselected(GENERAL_DATA[DATE]),GENERAL_DATA[DATE]) //
var _min = eomonth(_max,-12) +1
var _calc = calculate(count(GENERAL_DATA[DOCUMENT_NUMBER]), filter(all(GENERAL_DATA), GENERAL_DATA[DATE] >=_min && GENERAL_DATA[DATE] <=_max))
return
if(isblank(_calc) && max(GENERAL_DATA[DATE]) <_max , 0, _calc)

 

But remember when you use date in the slicer and try using that for a bigger date range than selected then you need all to remove filter

  • T_von_Axt's avatar
    T_von_Axt
    Helper I
    4 years ago

    Hello again amitchandak,

     

    It worked now, but when I insert the measure into a table, it keeps loading but shows nothing.

    Do you think this is related to the data volume (12 million rows)?

    Thank you in advance.