Forum Discussion
Dynamic Fiscal Calendar Slicer Using DAX – Default to Latest Period Post Refresh
I’m working on a Power BI report with a Date hierarchy slicer built on a custom fiscal calendar (FY starting in April). The hierarchy includes Fiscal Year, Fiscal Quarter, and Month.
Currently, I’ve implemented a dynamic date table using MIN/MAX from the fact table (spendcube[posting_date]) and created calculated columns for FY, Quarter, and Month using DAX.
However, I’m facing a usability challenge:
The slicer does not automatically default to the latest Fiscal Year, Quarter, and Month after each data refresh.
Since the dataset is refreshed monthly, we are manually updating slicer selections and handling new fiscal year transitions, which is not scalable.
Requirement:
I need a fully dynamic solution where:
The slicer automatically defaults to the latest available Fiscal Year, Quarter, and Month based on the data refresh.
It should seamlessly handle fiscal year rollovers (April–March cycle) without manual intervention.
The solution should be robust and work within Power BI Desktop (and Service if applicable).
What is the recommended approach to achieve this behavior? Additionally, what would be the best practice to implement a dynamic default selection for a fiscal hierarchy slicer in Power BI?
Reference DAX:
FY_Calendar_Posting =
VAR MinDate = MIN(txn_tbl[pos_date])
VAR MaxDate = MAX(txn_tbl[pos_date])
RETURN
FILTER(
ADDCOLUMNS(
CALENDAR(MinDate, MaxDate),
"MM", MONTH(EDATE([Date], -3))
),
NOT(ISBLANK([Date]))
)
FY =
VAR check = MONTH('FY_Calendar_Posting'[Date]) >= 4
VAR curr_year = YEAR('FY_Calendar_Posting'[Date])
VAR next_year = YEAR('FY_Calendar_Posting'[Date]) + 1
VAR prev_year = YEAR('FY_Calendar_Posting'[Date]) - 1
RETURN
IF(check, "FY" & curr_year & "-" & RIGHT(next_year, 2),
"FY" & prev_year & "-" & RIGHT(curr_year, 2))
Quarter = FORMAT(EDATE(FY_Calendar_Posting[Date], -3), "\QQ")
MMM = FORMAT('FY_Calendar_Posting'[Date],"MMM")
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.
5 Replies
Hii Maheshbabu1603
Power BI cannot dynamically change a slicer’s selected value using DAX after refresh. Slicer selections are report UI state, not model logic. The correct approach is to create a dynamic Is Latest Fiscal Period flag in the calendar table or a measure based on MAX(fact[posting_date]), then use that flag as a page/visual filter, or replace the slicer with a disconnected period selector such as “Latest Period / Custom / All”. This will handle April-March fiscal rollover automatically, but it will not physically auto-select values inside the slicer.
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.
Hi Maheshbabu1603,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
cengizhanarslan , danextian & rohit1991 ,Thanks for your prompt responseThank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportHi @Maheshbabu1603,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
