Date calculations are all US based.

[designertechno]

Trying to do simple things like subtract one date from another in Power BI Desktop and the results are all US based.

Days = DATEDIFF("01/01/2018", "10/01/2018", DAY)

Result of the above is 273 instead of 9.

Doing this in excel produces 9, so it's not likely an OS problem:

= DATEDIF("1/01/2018", "10/01/2018", "D")

How to set the locale of the PBI Desktop?

Version : 2.61.5192.601 64-bit (August 2018)

Options Regional Settings: English New Zealand

OS Window Display Settings : New Zealand English

OS Region : Formats / Local and System Locale all New Zealand English

[Thejeswar]

Hi @designertechno,

Power BI always sees your date value in the mm/dd/yyyy format

If your dates are 01/01/2018 and 10/01/2018, they are seen as Jan 1st, 2018 and Oct 1st, 2018 and hence the DateDIFF is 273. The Code is below

Measure 2 = DATEDIFF("01/01/2018", "10/01/2018", DAY) 

Measure 2 Output is 273 Days

Where as when your dates are 01/01/2018 and 01/10/2018 they are understood as Jan 1st, 2018 and Jan 10th, 2018, thereby giving the DATEDIFF as 9 days. The Code follows

Measure = DATEDIFF("01/01/2018", "01/10/2018", DAY) 

Measure Output is 9 Days

As you said, Power BI date are read with US locale.

To change the locale setting in your PBI Desktop, do the following (This applies only to the current file, as this is a current file level setting)

1. Go to File

2. Options and settings -> Options -> Regional Settings

PFB the screenshotLocale

 But as far as I know this will not work when you publish the report to PBI Service.

Just check with your Administrator if there is any locale settings at your tenant level. But this will change the locale for all the reports and PBI Service. So think before proceeding with this.

Speaking about your system local changes, I think You definitely can't ask all your users to change their system locale.

Alternatively, an easy workaround is Convert your dates to US date format of mm/dd/yyyy

Happy DAXing..:)

[Anonymous]

Use DATE function to fix this issue. Modify your DATEDIFF function as below

Days = DATEDIFF(DATE(2018,1,1),DATE(2018,1,10),DAY)

[designertechno]

Hi Jessica,

Thanks but that doesn't fix my issue. That would involve wrapping and manually breaking apart every date in a time series. 

The issue Ive posted about is the same if its a column of data so what you are suggesting is impractical.

I just need Power BI to respect my current locale.

[Anonymous]

This is just an example. You can modify this DAX to make it work on your env. As the example below, use Min/Max function to get the min/max date in your table. And then fill into the DATEDIFF function. 

DATEDIFF itself only counts date in MM/DD/YYYY format. 

Measure =
DATEDIFF (
    DATE ( YEAR (
        MIN ( MARMS[Date] )
    ), MONTH (
        MIN ( MARMS[Date] )
    ), DAY (
        MIN ( MARMS[Date] )
    ) ),
    DATE ( YEAR (
        MAX ( MARMS[Date] )
    ), MONTH (
        MAX ( MARMS[Date] )
    ), DAY (
        MAX ( MARMS[Date] )
    ) ),
    DAY
)

 

[designertechno]

Would you happen to know where this limitation is documented? Seems like a glaring oversight if true?

The problem is actually locale however as 

Dayyy = DAY("01/10/2018")

 returns 10.