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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Kira_Net
Helper I
Helper I

How to create a dynamic start and end date for DATESBETWEEN function?

Hey there,
I have a calculation created by datesbetween. I used Today()-7 function for the start and Today() for end date. BUt now I want to change it with a dynamic date which comes from my date slicer. Can anyone help me on this please?

1 ACCEPTED SOLUTION

@Kira_Net 

 

Add the variable in a measure to see whether it returns the correct date 

e.g.

 

Measure MIN_DATE = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Replace today()-7 with min(calendar[date]) and today() with max(calendar[date]).  Ensure that the Date slicer is built from the Calendar Table. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
themistoklis
Community Champion
Community Champion

Hello @Kira_Net 

 

Considering you have a Calendar table, you need to write 2 measures or variables and call these measures in datesbetween function.

 

Measure = 

var _min_date = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

var _max_date = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

RETURN

CALCULATE(SUM('Table'[Sales]),
DATESBETWEEN('Calendar'[Date], _min_date , _max_date
)
)

I have similar approach for my problem statement to count no. of rows between two dates
but with this solution, its showing me the count of all rows. 

Hey @themistoklis Thank you. I used the variable for the start and end date but it still showing me blank on my score card. the calculation works fine when I use today() and today ()-7 for start and end date but it only gives me the last week result. I wanted to create the flexeblity for users to filter out date using slicer.

 

@Kira_Net 

 

Add the variable in a measure to see whether it returns the correct date 

e.g.

 

Measure MIN_DATE = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

speedramps
Community Champion
Community Champion

Create a detached calendar (list of dates).

Drad calender to a slicer with a slicer bar (with min and max range)

 

Create dax measure ....

 

VAR mindate = MIN(calandar(date))
VAR maxdate = MIN(calandar(date))
RETURN
DATEDIFF(mindate , maxdate , DAY )

 

I am not sure if this is exactly what you need, but I hope you can change the value as required.

 

Please click the thumbs up and accept as solution button. Thank you !   😎

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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