cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dats
Resolver I
Resolver I

Customized Date Slicer by using text values

hi,

 

So, so far I have a simple Table. Containing all dates between the 07.12.2019 and 01.04.2023 in the date column the Weeknumber and year as numbers and a combined column that shows a combination of the weeknumber (either e.g. 01 or  49) a dot and the respective year as a text.

Date Table.PNG

 

No I created a slicer tha gives me the option to choose dates between the 07.12.2019 and 01.04.2023.

 

Slicer.PNG

So far so good. That is the visual I would like to use. But I would like to display the column "FinalFormat" rather then the date. So the slicer should display the values in this format "49.2019" to "13.2023". This I could not get to work.

 

Is there a way to tisplay text in this slicer or another way to display this formatted week year combination rather then the actual dates?

 

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @dats ,

 

How about this?

 

1. Create a column.

FinalFormat 2 = 'Calendar'[Year] & FORMAT ( 'Calendar'[WeekNumber], "00" )

finalformat2.PNG

 

2. Sort [FinalFormat] by [FinalFormat 2].

sort.jpg

 

3. Create two tables and Repeat step 2 in each table.

From = SUMMARIZE('Calendar','Calendar'[FinalFormat],'Calendar'[FinalFormat 2])
To = SUMMARIZE('Calendar','Calendar'[FinalFormat],'Calendar'[FinalFormat 2])

relationship.PNG

 

4. Create a Measure.

Measure = 
VAR From_ =
    SELECTEDVALUE ( 'From'[FinalFormat 2] )
VAR To_ =
    SELECTEDVALUE ( 'To'[FinalFormat 2] )
RETURN
    SWITCH (
        TRUE (),
        From_ = BLANK ()
            && To_ = BLANK (), 1,
        From_ = BLANK ()
            && To_ <> BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) <= To_, 1 ),
        From_ <> BLANK ()
            && To_ = BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) >= From_, 1 ),
        From_ <> BLANK ()
            && To_ <> BLANK (), IF (
            MAX ( 'Calendar'[FinalFormat 2] ) >= From_
                && MAX ( 'Calendar'[FinalFormat 2] ) <= To_,
            1
        )
    )

 

5. Create two slicers.

from.PNG

to.PNG

 

6. Create other visuals with "Filters on this visual": [Measure] is 1.

measure.PNG

 

7. Test.

finalformat.gif

 

For more details, please check the attached PBIX file.

 

 

Best Regards,

Icey

 

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

View solution in original post

6 REPLIES 6
orbe
Frequent Visitor

Hi @Icey 

Your method is successful, I implemented your idea with a simple date in text format. The problem is that I can't add the filter to the page but only to one visualization and therefore, unfortunately, it doesn't meet my needs

Icey
Community Support
Community Support

Hi @dats ,

 

How about this?

 

1. Create a column.

FinalFormat 2 = 'Calendar'[Year] & FORMAT ( 'Calendar'[WeekNumber], "00" )

finalformat2.PNG

 

2. Sort [FinalFormat] by [FinalFormat 2].

sort.jpg

 

3. Create two tables and Repeat step 2 in each table.

From = SUMMARIZE('Calendar','Calendar'[FinalFormat],'Calendar'[FinalFormat 2])
To = SUMMARIZE('Calendar','Calendar'[FinalFormat],'Calendar'[FinalFormat 2])

relationship.PNG

 

4. Create a Measure.

Measure = 
VAR From_ =
    SELECTEDVALUE ( 'From'[FinalFormat 2] )
VAR To_ =
    SELECTEDVALUE ( 'To'[FinalFormat 2] )
RETURN
    SWITCH (
        TRUE (),
        From_ = BLANK ()
            && To_ = BLANK (), 1,
        From_ = BLANK ()
            && To_ <> BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) <= To_, 1 ),
        From_ <> BLANK ()
            && To_ = BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) >= From_, 1 ),
        From_ <> BLANK ()
            && To_ <> BLANK (), IF (
            MAX ( 'Calendar'[FinalFormat 2] ) >= From_
                && MAX ( 'Calendar'[FinalFormat 2] ) <= To_,
            1
        )
    )

 

5. Create two slicers.

from.PNG

to.PNG

 

6. Create other visuals with "Filters on this visual": [Measure] is 1.

measure.PNG

 

7. Test.

finalformat.gif

 

For more details, please check the attached PBIX file.

 

 

Best Regards,

Icey

 

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

TomMartens
Super User
Super User

Hey @dats ,

 

unfortunately, this is not possible.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I could not find it but might it be possible to create a custom date format containing the week number and the year rather then using text ?

Hey @dats ,

 

this article describes how to create custom formats: https://blog.gbrueckl.at/events-in-progress/

And here is also a video: https://www.youtube.com/watch?v=72qQrPw4RuE

 

I'm not sure if the format will be honored inside a slicer, make sure that you check also the list view inside the slicer.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks @TomMartens .

I saw that post/video already. Couldn't find the solution to my problem yet. I will google more..

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.