Forum Discussion
How can I set Slicer that defaults dynamically to the current Financial Year
Hi All,
I am trying to work out the best way where I can have the default dates set to the current financial year. I can filter it out dynamically but I need to have the sliders able to look back in time and in to the future. I need this to be date slicer with the slider.
Has anyone done this before? Any assistance appreciated.
Anonymous , First create a date table with Finacial Year details
DateTable =
VAR BaseCalendar =
CALENDAR(
DATE(2010, 1, 1),
DATE(2030, 12, 31)
)
RETURN
ADDCOLUMNS(
BaseCalendar,
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)Add calculated column for financial year
FinancialYear =
IF(
MONTH([Date]) >= 7,
YEAR([Date]),
YEAR([Date]) - 1
)FinancialYearLabel =
IF(
MONTH([Date]) >= 7,
YEAR([Date]) & "-" & YEAR([Date]) + 1,
YEAR([Date]) - 1 & "-" & YEAR([Date])
)Add slicer and use Date column from date table
To set the default date range to the current financial year, you can use a measure to dynamically calculate the start and end dates of the current financial year:
CurrentFYStart =
DATE(
IF(
MONTH(TODAY()) >= 7,
YEAR(TODAY()),
YEAR(TODAY()) - 1
),
7,
1
)CurrentFYEnd =
DATE(
IF(
MONTH(TODAY()) >= 7,
YEAR(TODAY()) + 1,
YEAR(TODAY())
),
6,
30
)Unfortunately, Power BI does not natively support setting default slicer values dynamically. However, you can achieve this by using bookmarks and buttons:
Set the slicer to the desired default range (current financial year).
Create a bookmark with this slicer setting.
Add a button to your report and link it to the bookmark. This button can be labeled "Reset to Current Financial Year" or similar.
1 Reply
- bhanu_gautam
Super User
Anonymous , First create a date table with Finacial Year details
DateTable =
VAR BaseCalendar =
CALENDAR(
DATE(2010, 1, 1),
DATE(2030, 12, 31)
)
RETURN
ADDCOLUMNS(
BaseCalendar,
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)Add calculated column for financial year
FinancialYear =
IF(
MONTH([Date]) >= 7,
YEAR([Date]),
YEAR([Date]) - 1
)FinancialYearLabel =
IF(
MONTH([Date]) >= 7,
YEAR([Date]) & "-" & YEAR([Date]) + 1,
YEAR([Date]) - 1 & "-" & YEAR([Date])
)Add slicer and use Date column from date table
To set the default date range to the current financial year, you can use a measure to dynamically calculate the start and end dates of the current financial year:
CurrentFYStart =
DATE(
IF(
MONTH(TODAY()) >= 7,
YEAR(TODAY()),
YEAR(TODAY()) - 1
),
7,
1
)CurrentFYEnd =
DATE(
IF(
MONTH(TODAY()) >= 7,
YEAR(TODAY()) + 1,
YEAR(TODAY())
),
6,
30
)Unfortunately, Power BI does not natively support setting default slicer values dynamically. However, you can achieve this by using bookmarks and buttons:
Set the slicer to the desired default range (current financial year).
Create a bookmark with this slicer setting.
Add a button to your report and link it to the bookmark. This button can be labeled "Reset to Current Financial Year" or similar.