Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
T_von_Axt
Helper I
Helper I

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:

 

T_von_Axt_0-1637317615649.png

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

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

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.

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.

@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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.