Dynamic last quarter slicer

Hi Prabha45 

 

Thanks to Sergii24 and tharunkumarRTK for the quick response.
Did the Sergii24 and tharunkumarRTK replies help you to solve the problem? If help is still needed, allow me to add three solutions that may meet your requirements.

 

Here is my sample data:

 

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2023, 1, 1), DATE(2024, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT(QUARTER([Date]), "0"),
    "YearQuarter", "Q" & FORMAT(QUARTER([Date]), "0") & "" & YEAR([Date])
)

 

 

The first solution:
1. Create a new column as follows:

 

YearQuarterSlicer = 
VAR CurrentYearQuarter = YEAR(TODAY()) * 10 + QUARTER(TODAY())
VAR LastYearQuarter =
IF(
    QUARTER(TODAY()) = 1,
    CurrentYearQuarter - 7,
    CurrentYearQuarter - 1
)
RETURN
IF (
YEAR(DateTable[Date]) * 10 + QUARTER(DateTable[Date]) = LastYearQuarter,
"LastQuarter",
DateTable[YearQuarter]
)

 

2. Creating a slicer with the “YearQuarterSlicer” field. See uploaded pbix file page1.

 

The second solution:
1. Create a new column as follows:

 

IsLastQuarter = 
VAR YearQuarterNumber = 
DateTable[Year] * 10 +
SWITCH(
    DateTable[Quarter],
    "Q1", 1,
    "Q2", 2,
    "Q3", 3,
    "Q4", 4
)

VAR CurrentYearQuarterNumber = 
YEAR(TODAY())*10 +
SWITCH(
    TRUE(),
    MONTH(TODAY()) <=3, 1,
    MONTH(TODAY()) <=6, 2,
    MONTH(TODAY()) <=9, 3,
    4
)

VAR LastYearQuarter = 
IF(
    MONTH(TODAY()) <= 3,
    (YEAR(TODAY()) -1) * 10 + 4,
    CurrentYearQuarterNumber -1
)

RETURN
IF(
    YearQuarterNumber = LastYearQuarter,
    TRUE,
    FALSE
)

 

2. Creating a slicer with the “YearQuarter” field, then drag “IsLastQuarter” into the filter box of the slicer visual and select “True”.
3. Here is my test result, when select “True” in the filter box of the slicer visual, it will dynamically display slicer options based on the current date. See uploaded pbix file page2.

 

 

The third solution:
Get a custom visual "Preselected Slicer" by clicking click the "Get more visual".
Please refer to this link for detailed steps:  How to dynamically select the default values we ne... - Microsoft Fabric Community

 

Best Regards,

Jarvis Tang

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