Dynamic Fiscal Calendar Slicer Using DAX – Default to Latest Period Post Refresh

Hi Maheshbabu1603 

Dynamically selecting the latest date/period in a slicer is not currently possible without doing some changes in Tabular Editor. Please refer to these vidoes

https://www.youtube.com/watch?v=iSQ9n1z9gYA

https://www.youtube.com/watch?v=MrEAZREQuXM

Step 1) Add a relative period column to FY_Calendar_Posting

Create a relative period label calculated column in your fiscal calendar table that tags each period relative to the latest data, then use that label as a slicer and filter with a fixed default selection that never needs updating.

Relative Period =
VAR _MaxDate =
    CALCULATE ( MAX ( FY_Calendar_Posting[Date] ), ALL ( FY_Calendar_Posting ) )
VAR _MaxFY =
    CALCULATE ( MAX ( FY_Calendar_Posting[FY] ), ALL ( FY_Calendar_Posting ) )
VAR _MaxQtr =
    CALCULATE (
        MAX ( FY_Calendar_Posting[Quarter] ),
        ALL ( FY_Calendar_Posting ),
        FY_Calendar_Posting[FY] = _MaxFY
    )
VAR _MaxMM =
    CALCULATE (
        MAX ( FY_Calendar_Posting[MM] ),
        ALL ( FY_Calendar_Posting ),
        FY_Calendar_Posting[FY] = _MaxFY,
        FY_Calendar_Posting[Quarter] = _MaxQtr
    )
RETURN
    SWITCH (
        TRUE (),
        FY_Calendar_Posting[MM] = _MaxMM
            && FY_Calendar_Posting[FY] = _MaxFY,  "Current Month",
        FY_Calendar_Posting[Quarter] = _MaxQtr
            && FY_Calendar_Posting[FY] = _MaxFY,  "Current Quarter",
        FY_Calendar_Posting[FY] = _MaxFY,          "Current FY",
        "Historical"
    )

Step 2) Use Relative Period as a slicer

Add Relative Period to a slicer and set the default selection to Current Month.

Step 3) Disable persistent filters from the report settings

With this disabled, every time the report loads it applies the default bookmark state instead of the last saved filter state, so the Current Month slicer selection is always the starting point for every user on every session.