Problem with Between Slicer - Date

[ZowieS]

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:

My Data

My Table is like this:

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

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

[v-jayw-msft]

Hi @ZowieS ,

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

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

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.

[ZowieS]

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 😑

[mahoneypat · Accepted solution]

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

[ZowieS]

Hi @mahoneypat,

Thank you!

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