cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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
Community Support

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

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.

4 REPLIES 4
Community Support

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

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.

Helper III

Thanks @v-yetao1-msft

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

Helper III

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.