Forum Discussion
MTD questions
Well assumption here is use in built function MTD/QTD/YTD, if I have to use dateadd function, not sure what is the purpose of MTD/QTD/YTD functions.
I'm sure this is nothing to do with my dataset and must be a way to resolve simple MTD.
From your previous examples, DATESMTD is working fine but you are trying to do something strange, which is DATESMTD but for the previous month, which is effectively a PARALLELPERIOD for potentially half a month or 20 days of the previous month or whatever. So, with DATEADD, you would be feeding it DATESMTD and then rewinding 30 days or whatever. That seems to be what you want to do in your latest posts and what you are trying to do unless I am severely misunderstanding something.
So, let's say it is the 11th of December, DATESMTD will give you 12/1/2015-12/11/2015 and then DATEADD with an interval of a month and -1 should give you 11/1/2015-11/11/2015.
Versus PARALLELPERIOD, which would give you 11/1/2015-11/30/2015.
Ultimately, I'm pretty confused here as you seem to want it both ways, in some of your posts, you want it to be one way and in some of your posts, you seem to want it to be the other way. The way that you seem to want from your original post would be to use PARALLELPERIOD, where it would give you the full date range for the previous month. That is what you seem to want from your original post, right?
Confused...
Yes agreed with following and that is what exactly I wanted to do.
So, let's say it is the 11th of December, DATESMTD will give you 12/1/2015-12/11/2015 and then DATEADD with an interval of a month and -1 should give you 11/1/2015-11/11/2015.
Versus PARALLELPERIOD, which would give you 11/1/2015-11/30/2015.
OK, but then I am confused by this statement from the original post:
"this is the total of April until last transaction date of May 27th whereas for April I expect the total until April 30th..."
PARALLELPERIOD will give you what is stated originally. Given DATESMTD of May 1st - May 27th, PARALLELPERIOD should return April 1st - April 30th.
Your DATEADD fucntion from the original post is returning April 1st - April 27th.
Here is once again:
If I'm slicing the data on April 30th, 2014, works great, results below.
Curr MTD is 26251 (includes April 01st to April 30th)
Prev MTD is 6871 (includes Mar 01st to Mar 31st)
If I'm slicing the data on May 31st, 2014, results are wrong and this is what I'm getting
Curr MTD is 24865(should includes May 01st - May 31st but we have data until May 27th) -> result is correct
Prev MTD is 22260 (it is only including data from April 01st - April 27th) -> result is wrong
I expect following reuslt in this case
Prev MTD is 26251 (April 01st - April 30th)
Hope it is clear.
Any other help/solution? I'm kind of stuck here not to move further my development until it is resolved.
Thanks!
P