How to make latest date in dataset the default selection for hierarchy date slicer in power BI

[aditi1997yadav]

I am trying to make the latest  date in my dataset as the default selection for hierarchy date slicer, however i am unable to do so..  i tried tons of videos nothing is working. any help would be appreciated.

[Anonymous · Accepted solution]

Hi，

Thanks for the solution Ritaf1983 , Kedar_Pande , danextian  and lbendlin offered, and i want to offer some more information for user to refer to.

hello @aditi1997yadav , based on your description, you can refer to the following solution.

If you want to select the latest date in date hierachy, the default slicer cannot offer the default selection , you can consider the following.

Create a measure.

Flag =
VAR _year =
    MAXX ( ALLSELECTED ( 'Calendar'[Date].[Year] ), [Date].[Year] )
VAR _quarter =
    MAXX ( ALLSELECTED ( 'Calendar'[Date].[QuarterNo] ), [Date].[QuarterNo] )
VAR _month =
    MAXX ( ALLSELECTED ( 'Calendar'[Date].[MonthNo] ), [Date].[MonthNo] )
RETURN
    IF (
        SELECTEDVALUE ( 'Calendar'[Date].[Year] ) = _year
            && SELECTEDVALUE ( 'Calendar'[Date].[QuarterNo] ) = _quarter
            && SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] ) = _month,
        1,
        0
    )

Then put it to the visual filter.

Output

Another solution is that you can consider to use the preselected slicer, it will display all date without hierachy, but it can preselect the latest date.

Dowload the visual from the Appsource

Then create a true/false table.

Then create a measure.

MEASURE =
IF (
    SELECTEDVALUE ( 'Calendar'[Date] )
        = MAXX ( ALLSELECTED ( 'Calendar'[Date] ), [Date] ),
    TRUE (),
    FALSE ()
)

Then create a preselected slicer visual, and input the following field to the slicer.

Output

It will select the latest date as a default value.

Best Regards!

Yolo Zhu

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

[Ritaf1983]

Hi @aditi1997yadav 
I attached a pbix with the example that I prepared please download and check if it helps.
Feel free to ask questions.

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

[aditi1997yadav]

i actually wanted for quarters as well, and when i add quarter it selects all the months in that latest quarter.. i wanted my default selection as year+quarter+month

[Ritaf1983]

Hi @aditi1997yadav 

It doesn’t matter at what level; everything works based on the same logic.
If you can’t adapt my solution to your logic, simply attach a link to a PBIX file with basic data such as your date, measure, and calendar table, and show the desired result.

[Kedar_Pande]

@aditi1997yadav 

You can check :

https://youtu.be/VaJ10ilXp6Y?si=jx1mdARYo21RcH2F

https://youtu.be/MYHG-QSM8qw?si=Wx5x-Cl6iXXn1fhl

https://youtu.be/JlVHsZUk1nc?si=Vb_mCbMidmBLS-NE

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[danextian]

Hi @aditi1997yadav 

If you want the latest date itself to be selected at each refresh then you need to use an external tool called tabular editor and leverage the Group by Columns property to "store a filter by using an alternate value, which represents the key of the entity." Power BI uses this key to store the filter, allowing the corresponding filter value in the visual to change dynamically.

Here's a demo: https://youtu.be/MrEAZREQuXM?si=mtCRhSpSXiaJcxGO

[lbendlin]

Is the table that holds that date in import mode?  Have you created a calculated column for the "Latest"  value?

Here is the general approach. Adjust as needed
- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app