Forum Discussion

Voose's avatar
Voose
Icon for Helper III rankHelper III
9 years ago
Solved

Date function not working as intended?

Hi All,   I have an interesting conundrum, this formula works across all dates in the calendar year upto the July 2018 from 2015, however there is one date it doesn't work for.    For the below f...
General Comment
Need Help
  • AlbertoFerrari's avatar
    AlbertoFerrari
    9 years ago

    Well, DATEADD should work just fine, I have never seen a bug with that functions, and it should be the way to go.

     

    If, on the other hand, you want to do it manually, then you can follow a simple technique. Create a Y12M column in the Date table that contains (Year * 12 + Month). With that column in place, you can freely move back and forth with months by adding to that value the offset.

     

    So, MAX ( Date[Y12M] - 3 ) results in three months ago, no matter which year you are into.

     

    But, if I were you, I would try to understand what's wrong with DATEADD, as this is the best way of expressing date offsets.

     

    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

Voose's avatar
Voose
Icon for Helper III rankHelper III
9 years ago

Hi Marcelbeug,

 

I think you may have misread the formula, I'm not adding days... I'm adding months and the place you've linked would be useful if I were adding more months or days than there are in a given month of day however I am not I am adding 3 Months in a particular scenario.

 

Thanks

 

Voose

AlbertoFerrari's avatar
AlbertoFerrari
Icon for Most Valuable Professional rankMost Valuable Professional
9 years ago

Well, DATEADD should work just fine, I have never seen a bug with that functions, and it should be the way to go.

 

If, on the other hand, you want to do it manually, then you can follow a simple technique. Create a Y12M column in the Date table that contains (Year * 12 + Month). With that column in place, you can freely move back and forth with months by adding to that value the offset.

 

So, MAX ( Date[Y12M] - 3 ) results in three months ago, no matter which year you are into.

 

But, if I were you, I would try to understand what's wrong with DATEADD, as this is the best way of expressing date offsets.

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

  • Voose's avatar
    Voose
    Icon for Helper III rankHelper III
    9 years ago

    AlbertoFerrari - I feel like I've just had a celebrity reply to my post! :smileyembarrassed:

     

    Even more embarrsing, I left my original test of the DateAdd function in my model and I've just gone back to look at it and all of the dates seem to be filled in.... the only difference I can see is in the data type being Date and not Date time, that being said switching it back doesn't seem to effect it.

     

    On an aside, please come to england for more talks :)

     

    Thanks

     

    Voose