Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all,
I need help with a sticky problem...
I have a date table that is broken in to "week ending" dates, and a data table that has records added according to the week ending date.
I am using a date slicer configured as "Before" so that the user has only 1 date to manipulate. I would like the table showing the data to filter on the maximum week ending date selected in the slicer.
I have also created a measure to display the appropriate week ending based on the date selected.
My data looks like this:
Date Table
Data Table
My Date slicer with Measure showing MAX(Date) looks like this:
And if it's adjusted, it looks like this
If the slicer is set to the largest date, then I only want the highlighted records to show:
Whereas if the earlier date is selected, only these highlighted records should show:
Rationale for using the date slicer in this format:
I currently use a date slicer as a single-select dropdown, and this works fine, but the date table records expand week by week, as does the data table records, and to get this to reflect the current (max) week, I have to manually set the new filter selection and then re-publish the report every week. Using the date range slicer configured in "Before" mode, I can add date records to the date table and it will constantly default to the largest date value when the data is refreshed, which saves the manual step and allows me to update the database and automate the refresh without opening the report in the desktop.
The help I need:
Thanks in advance...
Adam
Solved! Go to Solution.
Hi @AFinster
You build a measure and drag the measure into filters and show values as 1 to achieve the goal.
You may try may way to calculate WeekEnding directly by measure.
WeekEnding =
VAR _Maxslicer =
MAX ( Sheet1[InsightDate] )
VAR _MAXWEEKEND =
MAXX (
FILTER (
ALL ( 'Weekly Insights' ),
'Weekly Insights'[Week Ending] <= _Maxslicer
),
'Weekly Insights'[Week Ending]
)
RETURN
IF (
MAX ( 'Weekly Insights'[Week Ending] ) = _MAXWEEKEND,
_MAXWEEKEND,
BLANK ()
)
Result:
You can download the pbix file from this link: Filter Table based on MAX of date slicer
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @AFinster ,
I have faced similar problem in past where I was asked to add max date as default feature, since client wanted to get the latest data. Not only this they had quite a few requirements in terms of presets for the Date slicer which does not come in Native slicer. What we ended up doing is we bought a custom visual named Date picker by Powerviz.
It had all the features they required so that was a life saver.
Here is a Screenshot from the visual.
I think you should check them out.
Here is a link if you want to check this visual - https://appsource.microsoft.com/en-us/product/powerbivisuals/truvizinc1674781244292.date-picker-by-p... (I believe they offer a free version too)
Hi all,
I accepted Rico's post, as it was the closest to the solution I discovered independently, with some inspiration from BI Elite (https://youtu.be/AZAL-QPn5Zc ).
My Measures look like this:
Quick update,
Both sample measures created an error regarding a circular reference using:
@AllisonKennedy I also tried the relative date slicer, but could not see how to have the user select a specific date ...
Links to sample pbix and source excel data if this helps:
Hi @AFinster
You build a measure and drag the measure into filters and show values as 1 to achieve the goal.
You may try may way to calculate WeekEnding directly by measure.
WeekEnding =
VAR _Maxslicer =
MAX ( Sheet1[InsightDate] )
VAR _MAXWEEKEND =
MAXX (
FILTER (
ALL ( 'Weekly Insights' ),
'Weekly Insights'[Week Ending] <= _Maxslicer
),
'Weekly Insights'[Week Ending]
)
RETURN
IF (
MAX ( 'Weekly Insights'[Week Ending] ) = _MAXWEEKEND,
_MAXWEEKEND,
BLANK ()
)
Result:
You can download the pbix file from this link: Filter Table based on MAX of date slicer
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@AFinster , based on what I got
To make it work best create week start and end in your calendar. Seem like you have friday to Thrusday calendar
The calendar is aatached after signature
or refer by blog for any other day week https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...
you can week like
measure =
var _max = date(allselected(Date), Date[Week end Date])
return
calculate([measure], filter(all(date), Date[Week end Date] =_max))
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |