MTD questions

[parry2k]

Hello all,

I have interesting question on MTD functionality and here is the question:

I have a date dimension as we know as best practice we should have date dimension and I do have one. I have a table with date and quantity sold. I wanted to create a report to show Current MTD with Prev MTD and this is what I did:

Created two measures:

CurrentMTD = CALCULATE(SUM('Cube'[Qty]),DATESMTD('Cube'[Date]))

PrevMTD = CALCULATE(SUM('Cube'[Qty]),DATEADD(DATESMTD('Cube'[Date]),-1,MONTH))

Added a filter on the graph for date and here is the result I'm getting and what I expect:

Case 1 = If  date on filter is April 30th, 2014, it works fine.

CurrentMTD = 26251 (This is total of April)

PrevMTD =  6871 (This is total of March)

Case 2 = if date on filter is May 31st, 2014, I don't get correct data:

CurrentMTD = 24865 (this is total of May)

Prev MTD = 22260 (this is total of April until last transaction date of May which is May 27th) whereas for April I expect the total until April 30th and that total will be 26251

I'm wondering if this is expected behaviour or i'm doing something wrong here. Regardless, whatever date I choose on a filter, I expect correct values for current and previous MTD. I  assume the same challenge will be with QTD and YTD.

Here is my sample data:

Date	Qty	MTD	YTD
01/03/2014	381	381	381
02/03/2014	223	604	604
03/03/2014	298	902	902
04/03/2014	270	1172	1172
05/03/2014	243	1415	1415
06/03/2014	191	1606	1606
07/03/2014	288	1894	1894
08/03/2014	273	2167	2167
09/03/2014	201	2368	2368
10/03/2014	203	2571	2571
11/03/2014	205	2776	2776
12/03/2014	181	2957	2957
13/03/2014	283	3240	3240
14/03/2014	299	3539	3539
15/03/2014	248	3787	3787
16/03/2014	187	3974	3974
17/03/2014	212	4186	4186
18/03/2014	199	4385	4385
19/03/2014	187	4572	4572
20/03/2014	186	4758	4758
21/03/2014	236	4994	4994
22/03/2014	242	5236	5236
23/03/2014	144	5380	5380
24/03/2014	181	5561	5561
25/03/2014	196	5757	5757
26/03/2014	193	5950	5950
27/03/2014	188	6138	6138
28/03/2014	218	6356	6356
29/03/2014	215	6571	6571
30/03/2014	127	6698	6698
31/03/2014	173	6871	6871	month total
01/04/2014	194	194	7065
02/04/2014	200	394	7265
03/04/2014	214	608	7479
04/04/2014	229	837	7708
05/04/2014	263	1100	7971
06/04/2014	141	1241	8112
07/04/2014	178	1419	8290
08/04/2014	144	1563	8434
09/04/2014	144	1707	8578
10/04/2014	162	1869	8740
11/04/2014	206	2075	8946
12/04/2014	203	2278	9149
13/04/2014	949	3227	10098
14/04/2014	1198	4425	11296
15/04/2014	1194	5619	12490
16/04/2014	1263	6882	13753
17/04/2014	1309	8191	15062
18/04/2014	1871	10062	16933
19/04/2014	1934	11996	18867
20/04/2014	402	12398	19269
21/04/2014	1320	13718	20589
22/04/2014	1150	14868	21739
23/04/2014	1178	16046	22917
24/04/2014	1312	17358	24229
25/04/2014	1867	19225	26096
26/04/2014	1972	21197	28068
27/04/2014	1063	22260	29131
28/04/2014	1318	23578	30449
29/04/2014	1136	24714	31585
30/04/2014	1537	26251	33122	month total
01/05/2014	1833	1833	34955
02/05/2014	2216	4049	37171
03/05/2014	1978	6027	39149
04/05/2014	910	6937	40059
05/05/2014	1278	8215	41337
06/05/2014	1163	9378	42500
07/05/2014	1154	10532	43654
08/05/2014	1364	11896	45018
09/05/2014	1887	13783	46905
10/05/2014	1905	15688	48810
11/05/2014	969	16657	49779
12/05/2014	1259	17916	51038
13/05/2014	1139	19055	52177
14/05/2014	1104	20159	53281
15/05/2014	1252	21411	54533
16/05/2014	1665	23076	56198
17/05/2014	1788	24864	57986
27/05/2014	1	24865	57987	month total

[leonardmurphy]

I wasn't able to recreate your original issue with the sample data given and a simple table visualization with all dates.

When I slice by 30/4/14, I see a Prev MTD result of 26251.

This means that, in my opinion, the behaviour you're expecting to see is the expected behaviour, and the fact you're not seeing it is a problem not related to the DAX formula.

I don't know what that problem is though.

Perhaps start from a fresh power bi file, just to see if you can recreate your own problem. If you can't, then see if you can find the difference between your original and your recreation. It could be something subtle to do with the relationships between tables, or the data itself. I'm really not sure.

[parry2k]

if you are slicing by 30/4/2014 then current mtd will be 26251 and prev mtd (March) will be 6871 which is work fine for me as well. See case #1 in my original question.

Issue is with when you slice it 31/5/2014 (case 2 in my original question)

Thanks,

[Greg_Deckler]

I think you should be using the PREVIOUSMONTH function:

https://support.office.com/en-US/article/PREVIOUSMONTH-Function-DAX-72fb8dda-6cd6-49e6-b9b4-8a5f6324...

[bherring1979]

PREVIOUSMONTH will work, but I have to ask if that date format is supported? It must be, since CurrentMTD is working.

[parry2k]

Somehow my reply disappeared, seems like previous month is for full month not MTD. So in that case, do i have to have two measures, one for previousmonth and one for MTD

[parry2k]

As per the description in previousmonth function, it will return all dates for the month and in that case MTD will not work.

Here is the what is says on this link https://support.office.com/en-US/article/PREVIOUSMONTH-Function-DAX-72fb8dda-6cd6-49e6-b9b4-8a5f6324...

This function returns all dates from the previous month, using the first date in the column used as input. For example, if the first date in the dates argument refers to June 10, 2009, this function returns all dates for the month of May, 2009.

[Greg_Deckler]

Use PARALLELPERIOD with month as the interval and -1.

https://support.office.com/en-us/article/PARALLELPERIOD-Function-f38ab5a7-d88c-48a8-9eb2-dc67d5aa3f4...

You would use DATESMTD function as your dates input

[parry2k]

Thanks for the reply and here is what I noticed in parallelperiod help, based on this, seems like, I cannot use it for MTD.

The PARALLELPERIOD function is similar to the DATEADD function except that PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. For example, if you have a selection of dates that starts at June 10 and finishes at June 21 of the same year, and you want to shift that selection forward by one month then the PARALLELPERIOD function will return all dates from the next month (July 1 to July 31); however, if DATEADD is used instead, then the result will include only dates from July 10 to July 21.

[Greg_Deckler]

Use DATEADD then:

https://support.office.com/en-US/article/DATEADD-Function-DAX-035e775e-6ef8-416e-816f-ebeeb826ba22

[parry2k]

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.

[Greg_Deckler]

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

[parry2k]

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.

[Greg_Deckler]

---

---

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.

[parry2k]

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.

[parry2k]

Any other help/solution? I'm kind of stuck here not to move further my development until it is resolved.

Thanks!

P