Year Slicer set to Default

[newgirl]

Hi!

This is the current format of the report I have. It shows the monthly volume and I want to add Year slicer on the page. However, I want the slicer to be dynamic, similar to having a slicer to "Today" as default.

But in my case, I want the slicer to select the "Reporting Year" by default.

This is what my Year table (which is connected to Calendar table and then connected to Volume table) looks like:

Year
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032

I added a new column which I will use for the slicer. The condition I need to be tagged as the "Reporting Year" is:

If the current date falls on the start of the year (Jan 1, 2021 / Jan 1, 2022 / Jan 1, 2023, etc), then the value under the "Year" column which is the previous year would be tagged as "Reporting Year". But if the current date is not the start of the year, then the value under the "Year" column which is equal to the current year would be tagged as "Reporting Year".

Below are the scenarios I expect:

If the current date is within Jan 2 to Dec 31, 2021

Year	Show
2018	2018
2019	2019
2020	2020
2021	Reporting Year
2022	2022
2023	2023
2024	2024
2025	2025

If current date is Jan 1, 2022 (it should still tag 2021 as the "Reporting Year)

Year	Show
2018	2018
2019	2019
2020	2020
2021	Reporting Year
2022	2022
2023	2023
2024	2024
2025	2025

If current date is within Jan 2, to Dec 31, 2022

Year	Show
2018	2018
2019	2019
2020	2020
2021	2021
2022	Reporting Year
2023	2023
2024	2024
2025	2025

If current date is Jan 1, 2023 (it should still tag 2022 as the "Reporting Year)

Year	Show
2018	2018
2019	2019
2020	2020
2021	2021
2022	Reporting Year
2023	2023
2024	2024
2025	2025

On Jan 2 to Dec 31, 2023

Year	Show
2018	2018
2019	2019
2020	2020
2021	2021
2022	2022
2023	Reporting Year
2024	2024
2025	2025

Below is my current DAX for the second column:

Show =
IF (
    TODAY () = DATE ( YEAR ( TODAY () ), 1, 1 )
        && 'Year'[Year]
            = YEAR ( DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) ),
    "Reporting Year",
    IF (
        YEAR ( TODAY () ) = 'Year'[Year],
        "Reporting Year",
        FORMAT ( 'Year'[Year], "####" )
    )
)

 

At first glance, the column looks fine but I'm not that confident with it and I want to try it if it really does work. I don't know how to make PBI think that today is really the start of the year for testing purposes, so I tried to tweak the formula instead to make the first statement true:

Show =
IF (
    TODAY () = DATE ( YEAR ( TODAY () ), 10, 24 )
        && 'Year'[Year]
            = YEAR ( DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) ),
    "Reporting Year",
    IF (
        YEAR ( TODAY () ) = 'Year'[Year],
        "Reporting Year",
        FORMAT ( 'Year'[Year], "####" )
    )
)

*fyi that October 24, 2021 is the actual date as of writing

After this "testing", my column shows up like this:

Looks like it's applying both IF statements that's why there are 2 years being tagged as the "Reporting Year", so it looks like my formula is not yet perfect?

Was the way how I tested my formula wrong? Or is my formula still really not perfect? Hope somebody can help me out how to fix it!

[mwegener · Accepted solution]

Hi @newgirl ,

try this.

Show =
--var _Date = TODAY()
VAR _Date =
    DATE ( 2021, 1, 1 )
RETURN
    IF (
        'Year'[Year]
            = YEAR ( _Date - 1 ),
        "Reporting Year",
        FORMAT ( 'Year'[Year], "####" )
    )