cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newgirl
Helper V
Helper V

Year Slicer set to Default

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.

ReportingYear 1.JPG

 

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

YearShow
20182018
20192019
20202020
2021Reporting Year
20222022
20232023
20242024
20252025

 

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

YearShow
20182018
20192019
20202020
2021 Reporting Year
20222022
20232023
20242024
20252025

 

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

YearShow
20182018
20192019
20202020
20212021
2022 Reporting Year
20232023
20242024
20252025

 

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

YearShow
20182018
20192019
20202020
20212021
2022 Reporting Year
20232023
20242024
20252025

 

On Jan 2 to Dec 31, 2023

YearShow       
20182018
20192019
20202020
20212021
20222022
2023Reporting Year
20242024
20252025

 

 

 

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], "####" )
    )
)

 

ReportingYear 2.JPG 

 

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:

ReportingYear 3.JPG

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!

1 ACCEPTED SOLUTION
mwegener
Super User
Super User

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], "####" )
    )
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

1 REPLY 1
mwegener
Super User
Super User

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], "####" )
    )
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.