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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.