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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
dcrow5378
Resolver I
Resolver I

Date Slicer Default to first day of month and Max data date

Hello,

I have a date slicer that I would like to set to the first day of the month and the latest data date.

The latest data date is always yesterday.

If I set the filter to "is in this month" then on the first day of the following month the data dates do not display correctly.

 

So basically on 4/1/21, the date range should be 3/01/21 - 03/31/21.

On 4/2/21 the date range should be 4/1/21 - 4/1/21.

On 4/3/21 the date range should be 4/1/21 - 4/2/21.

 

This works fine all month until the month changes then I have issues.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @dcrow5378 

According to your date, create two measures to return the date you want firstly .

The latest data date = SELECTEDVALUE('Table'[Date])-1

The first day of the month = EOMONTH('Table'[The latest data date],-1)+1

Ailsa-msft_0-1619399796067.png

Then use the two measures as  the start time and end time of the time period .

  

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @dcrow5378 

According to your date, create two measures to return the date you want firstly .

The latest data date = SELECTEDVALUE('Table'[Date])-1

The first day of the month = EOMONTH('Table'[The latest data date],-1)+1

Ailsa-msft_0-1619399796067.png

Then use the two measures as  the start time and end time of the time period .

  

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @Anonymous

I used this with the following to filter my Date Range Slicer:

 
DateRange =
VAR MaxDt = MAX(DateDim[Date])
VAR FirstDt = EOMONTH(MaxDt,-1)+1
RETURN

IF(DateDim[Date] >= FirstDt && DateDim[Date] <= MaxDt, TRUE(),FALSE())
AllisonKennedy
Super User
Super User

@dcrow5378  The only way I can think to solve this would be to create a custom column in Power Query and set the date filter on that custom column? Basically shift the [Date] column so that the filter shows the dates you want when you want them? 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

So basically I need a formula for max date in table and the first day of the max dates month.
Then apply the filter for if the date falls in this range. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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