Date Table slicer must pick the Max date when Academic Year is selected from another slicer.

[Anonymous]

Hi ,
I have created a date table using DAX which starts from week in which Sepetember 1st falls in. For example in Academic Year 2021/22 The weekstartdate is 30/08/21 ,2022/23 it is 29/08/22 and for 2023/24 it is 28/08/24. I have created a Academic Year column using DAX 

AcademicYear =

IF(

    DateTable[Week] >= 36,

    DateTable[Year] & "/" & DateTable[Year] - 1999,

    DateTable[Year] - 1 & "/" & DateTable[Year] - 2000

)
The expectation is if a user selects a Academic Year, the Max value in Date slicer must change(slicer with between). For example for 2021/22 ,max is 28/08/24 and similarly for 2022/23 , max date is 27/08/24. (Screenshot, I have manually updated)

Not sure what I am missing. Any help is appreciated!
Thank you

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

We can get the three point in the "Visualizations", and we click the "Get more visual":

Then we find the "Preselected Slicer" and add the visual to our Power Bi Desktop.

First , we need to create a table with one column , and the value is True and False, like this:

Second , we can create a measure to define which the date we need to default preselected. If we need to default preselect then we return True else return False.

slicer date =
var _select=SELECTEDVALUE('Slicer_Table'[Academic Year])
var _year=VALUE(LEFT(_select,4))
var _mindate=
MINX(FILTER(ALL('Table'),
YEAR('Table'[Date])=_year),[Date])
var _maxdate=
MINX(
    FILTER(ALL('Table'),YEAR('Table'[Date])=_year&&WEEKNUM('Table'[Date],2)=36),[Date])
return
IF(
    MAX('Table'[Date])>=_mindate&&MAX('Table'[Date])<=_maxdate,TRUE(),FALSE())

Then we can put the 'Table'[Date] in the Fields , [slicer date] in the Pre Selection , 'Dirty Table'[Dirty Status] in the Dirty Status, like below screenshot: 

We can then dynamically display the date range in the slice visualization by default, and when we select another date, we can restore the default selection by simply clicking the Reset button in the upper right corner.

Refer to :

This is the related document, you can view this content:

Solved: How to use the 'between' option on a slicer for a ... - Microsoft Fabric Community

Solved: The "Between dates" slicer does not respect other ... - Microsoft Fabric Community

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