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

[dcrow5378]

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.

[v-yetao1-msft · Accepted solution]

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

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.

[dcrow5378]

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

[AllisonKennedy]

@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? 

[dcrow5378]

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.