How do I set customize the max date in a Power BI Slicer with DAX Functions?

[EnsignLuke]

I need to create a dynamic slicer that sets the max value of a drop-down school semester to 1 semester after the current semester.

semester codes are in the following format:

Example: 2191 (2019 Winter Semester - 219 denotes the year, the last number denotes the semester - 1 for Winter, 6 for Spring, 9 for Fall)

More examples:

2186 = Spring 2018

2209 = Fall 2020

I would like to apply a function to the slicer to help it recognize what semester we are currently in based on today's date, then use that semester or the following semester as the max option in the slicer. At the moment, I have to manually select the semesters that show in the slicer, which means there is either redundant options, or new semesters won't show until I add them.

Is this possible with Power BI, and if so, how would I go about accomplising it? I would have no problem coming up with dax code to determine what semester we're in, but I don't know where to start when it comes to applying that to the slicer drop-down.

Thanks for any help!

[Anonymous · Accepted solution]

Hi  @EnsignLuke ,

I created some data:

Here are the steps you can follow：

1. Use Enter data to create a table.

In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

Date =
var _year=
VALUE(
LEFT('Slice_table'[Slice],1)&"0"&MID('Slice_table'[Slice],2,2))
var _right =
VALUE(
RIGHT('Slice_table'[Slice],1))
return
DATE(
    _year,_right,1)

3. Create measure.

max_date =
var _select=SELECTEDVALUE('Slice_table'[Slice])
var _date=CALCULATE(MAX('Slice_table'[Date]),FILTER(ALL(Slice_table),'Slice_table'[Slice]=_select))
return
CALCULATE(MIN('Slice_table'[Date]),FILTER(ALL(Slice_table),'Slice_table'[Date]>_date))

Flag1 =
var _select=SELECTEDVALUE('Slice_table'[Slice])
return
IF(
    MAX('Slice_table'[Date])<=[max_date],1,0)

Flag2 =
var _select=SELECTEDVALUE('Slice_table'[Slice])
var _date=CALCULATE(MAX('Slice_table'[Date]),FILTER(ALL(Slice_table),'Slice_table'[Slice]=_select))
return
  IF(
   MAX('Table'[Date])>=_date&&MAX('Table'[Date])<=[max_date],1,0)

4. Click [Slice] – Column tools – Sort by column – [Index].

5. Use [Slice] of the Slice table as the slicer, and put [Flag1] in the Filter of the slicer, and set is=1.

The slicer is to sort the term or the next term as Sort descending as the largest option in the slicer

6. In the Filter that placed [Flag2] in the table visual, set is=1.

7. Result:

Best Regards,

Liu Yang

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

[speedramps]

Lots of Power BI novices make the mistake of adding a MMM-YYYY to a single selection slicer, then publishing the report.

They expect the reports to automticaly refesh, but the slicers default will remain the same eg May-2022 in June, July, August for ever.

The solution is to create a period pick list with "Current Semester" and then a list of semisters Spring 2018, Summer 2018,  Fall 2018, etc. Then use the period list in your slicer with "Current Semester" as the default.

You then need to create query to populate a briding table with a list of periods and date,

and build relationships.

This video shows you how to do it, but you will need to adapt the solution for Semester

https://youtu.be/8Mvr_AIw2DU

It will take you along time to build it, but if you are working in education then you we reuse the Calendar peiod pick list lots of times.

I helped you so now please help me.

Click the thumbs up and / or Accept As Solution.

Thank you 😀