Forum Discussion

designertechno's avatar
designertechno
Frequent Visitor
8 years ago

Date calculations are all US based.


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

 

 

 

 

 

 

Show and Tell

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      designertechno
      Frequent Visitor
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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
)

         

         

  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    8 years ago

    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..:)