cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Problem with Between Slicer - Date

Hi,

 

My Goal:

I'm trying to get my between slicer with dates working but I'm unseccefull.

I would like to slice my visuals based on date. Both Create and Closed date. Something like this:
217dba7a-5b71-4755-962a-10a4d8204fd8

 

My Data

My Table is like this:

2020_06_04_11_36_53_Window.png

Name - Format

Incident No. - Text

Created Date - Text

Closed Date - Text

and the time is in 24h format and the date is dd/MM/yyyy

It comes from XLSX file on a Sharepoint Online Folder location.

The problem?

When I try change the format from Text to Date/Time or just Date it switches places on Day and Month get formated as MM/dd/yyyy.

Exampel: 12th of May 2020 is changed to 5th of December 2020

And it dosn't mather if I Duplicate the columm and then change it or

Create a new columm based on one of the dates colums and then change it (ex: " New column = Format([Created Date], "dd/MM/yyyy") ".

 

I also created a Calendar Table but I'm unseccefull there too 😐

This is the relationship between the to Tables

2020-06-04 12_42_03-Window.png

My table Calendar code:

 

 

Calendar = 
VAR BaseCalendar  = CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2025, 12, 31 ) )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
        RETURN ROW (
            "Day", FORMAT (BaseDate, "dd"),
            "Month Name", FORMAT ( BaseDate, "mmmm" ),
            "Month Short name", FORMAT ( BaseDate, "mmm" ),
            "Year", YearDate,
            "Month Number", FORMAT(MonthNumber, "MM"),            
            "Week Number", WEEKNUM([Date]),
            "DateKey", FORMAT(BaseDate,"ddmmyyyy")
        )
    )

 

 

 

Thank you

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Prblem with Between Slicer - Date

One other thing to try.  Make sure your first Changed Type step for that column sets it to text type, use Column From Examples to get the format you want based on that column selection, and then change to Date.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Prblem with Between Slicer - Date

Hi @ZowieS ,

 

Change Locale for import under Options to English (United Kingdom).

4.PNG

Then change the Date type under Column tools to Date/time.

5.PNG

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Regular Visitor

Re: Prblem with Between Slicer - Date

Hi @v-jayw-msft,

 

Thanks for your reply!

I've tested that with both Swedish (Sweden), English (Sweden) and Enligsh (United Kingdom) but it still switch places on the Day and Month 😑

 

 

Highlighted
Community Champion
Community Champion

Re: Prblem with Between Slicer - Date

One other thing to try.  Make sure your first Changed Type step for that column sets it to text type, use Column From Examples to get the format you want based on that column selection, and then change to Date.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

Highlighted
Regular Visitor

Re: Prblem with Between Slicer - Date

Hi @mahoneypat,

 

Thank you!

That worked with "Column From Examples" for some reson 🤔

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors