How do I use Date Slicers in Formulas?

[T_von_Axt]

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 column called Document Date

This is what my slicer looks like.

What I want is to check if a document was received in a period that refers to the end date of that slicer, minus 12 months, and put the number of the document in a column. If the document was not received in the said period, the column will have a value of 0.

I checked lots of solutions, but all of them use 2 columns for the date slicer, but for my use case I want to use just one.

Thank you in advance for your support.

[amitchandak · Accepted solution]

@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]

Hi again @amitchandak,

It is not quite what I want to achieve yet.

Do you know how can I get the date used as the end date in that slicer?

Thank you in advance.

[T_von_Axt]

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]

@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]

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.