cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
T_von_Axt
Frequent Visitor

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)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!