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 ...
Show and Tell
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
)

     

     

    • designertechno's avatar
      designertechno
      Frequent Visitor
      8 years ago

      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.