Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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.
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.
Thanks @Anonymous
I used this with the following to filter my Date Range Slicer:
@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?
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.
User | Count |
---|---|
120 | |
69 | |
69 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
53 |