Formatting different for different types of Date Slicer

[MasterP]

I've researched this issue and found some content, but nothing directly on point. Apologies if this has been answered already.

• I am using a Date Slicer in Power BI Desktop
• The filter field is of type "Date" and has format "dd-mmm-yy"
• The Regional Format on my local machine is set to "English (United States)" and I have altered the Short Date format to "dd-mmm-yy"
• When I am using the slicer in Dropdown mode, my dates are formatted as "dd-mmm-yy" (this is what I want)
• When I switch to Between mode, the dates are formatted "mm/dd/yyyy" (this is not what I want)

My questions:

1. Why? 
2. What exactly is the difference between the Date data type and the Date/Time data type? I feel like Date is a fake data type (for example, it is not an option for the second argument to the DAX 'Convert' function).
3. Anything I can do to get my regional format respected in a Between Slicer?

[Icey · Accepted solution]

Hi @MasterP ,

Please check:

1. Why? 

---

Power BI will choose "dd/mm/yyyy"(United Kingdom) or "mm/dd/yyyy"(United States) format based on your region setting when a slicer is in "Between" mode. This setting cannot be customized.

2. What exactly is the difference between the Date data type and the Date/Time data type? I feel like Date is a fake data type (for example, it is not an option for the second argument to the DAX 'Convert' function).

---

Date/Time – Represents both a date and time value. Underneath the covers, the Date/Time value is stored as a Decimal Number Type. So you can actually convert between the two. The time portion of a date is stored as a fraction to whole multiples of 1/300 seconds (3.33 ms). Dates between years 1900 and 9999 are supported.

Date – Represents just a Date (no time portion). When converted into the model, a Date is the same as a Date/Time value with zero for the fractional value.

Reference: Date/time types

3. Anything I can do to get my regional format respected in a Between Slicer?

---

The format in the slicer Between mode is already displayed according to the region. But the format you want is not supported in Power BI currently. You can post your idea on Power BI Ideas. It is a place for customers to provide feedback about Power BI.

Best Regards,

Icey

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

[simrantuli]

Hi @MasterP,

I don't think that's possible.

The format for Date is always 'dd-mm-yyyy' when used in a 'Between' slicer. I don't think we can change that.

Best Regards

Simran Tuli

[MasterP]

Do you mean it's always "mm/dd/yyyy"?

[simrantuli]

It always uses the 'Short Date' format.

And the Short Date has no format with 'MMM' in it.

Best Regards

Simran Tuli

[MasterP]

@simrantuli mine actually does have that option. but it is still not respected.

[Anand24]

Hi @MasterP ,

You will need to change the format of date field from Power Query editor. Change it by "Using Locale..." and select your desired date format.

Usually the date slicer should take the format of date field that has been defined. So if it formatted as "dd-mm-yyyy", it should take that as slicer to and from. If still it is not taking it, then this might be a limitation of slicer and you can submit an idea for this to be taken care of in future. 

Date/Time consists of hours/minutes/seconds in its format for analysing in those level. That's basically the difference between Date and Date/Time.

Don't forget to give thumbs up if this helped you and accept this as a solution if it solved your query !!!

[Anand24]

Don't forget to give thumbs up if this helped you and accept this as a solution if it solved your query !!!

[Greg_Deckler]

@MasterP - Correct, mm/dd/yyyy. Date is sort-of-kind-of a fake data type it is more or less the number of seconds since midnight 12/30/1899 if I remember correctly. So it is really just a numeric data type. Anyway, the whole number part is the number of days and the decimal part is fractions of a day.