Forum Discussion
Helper IIIDate function not working as intended?
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
Community Champion- MarcelBeug
Microsoft's information on the DATE function, clearly explains what happens if you supply a day number > last day of the month (and lots of other information as well)
VooseHi 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
- MarcelBeug
If you add 3 months to DATE(2018,3,31) then you end up in DATE(2018,6,31), which returns July 1, 2018, because June has only 30 days. I don't think I misread anything.
- Voose
Hello Smoupre!
Initially I started out using the Dataadd function but that didn't give the desired result, it basically added months when I wanted which was good but seemingly randomly it just returned blank for 4 dates (there are about 5k dates in this table) and I couldn't find any comminality between those 4 so I switched to this method instead :(
Thanks
Voose
