Forum Discussion

parry2k's avatar
parry2k
Icon for Super User rankSuper User
10 years ago

MTD questions

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:

 

 

 

DateQtyMTDYTD 
01/03/2014381381381 
02/03/2014223604604 
03/03/2014298902902 
04/03/201427011721172 
05/03/201424314151415 
06/03/201419116061606 
07/03/201428818941894 
08/03/201427321672167 
09/03/201420123682368 
10/03/201420325712571 
11/03/201420527762776 
12/03/201418129572957 
13/03/201428332403240 
14/03/201429935393539 
15/03/201424837873787 
16/03/201418739743974 
17/03/201421241864186 
18/03/201419943854385 
19/03/201418745724572 
20/03/201418647584758 
21/03/201423649944994 
22/03/201424252365236 
23/03/201414453805380 
24/03/201418155615561 
25/03/201419657575757 
26/03/201419359505950 
27/03/201418861386138 
28/03/201421863566356 
29/03/201421565716571 
30/03/201412766986698 
31/03/201417368716871month total
01/04/20141941947065 
02/04/20142003947265 
03/04/20142146087479 
04/04/20142298377708 
05/04/201426311007971 
06/04/201414112418112 
07/04/201417814198290 
08/04/201414415638434 
09/04/201414417078578 
10/04/201416218698740 
11/04/201420620758946 
12/04/201420322789149 
13/04/2014949322710098 
14/04/20141198442511296 
15/04/20141194561912490 
16/04/20141263688213753 
17/04/20141309819115062 
18/04/201418711006216933 
19/04/201419341199618867 
20/04/20144021239819269 
21/04/201413201371820589 
22/04/201411501486821739 
23/04/201411781604622917 
24/04/201413121735824229 
25/04/201418671922526096 
26/04/201419722119728068 
27/04/201410632226029131 
28/04/201413182357830449 
29/04/201411362471431585 
30/04/201415372625133122month total
01/05/20141833183334955 
02/05/20142216404937171 
03/05/20141978602739149 
04/05/2014910693740059 
05/05/20141278821541337 
06/05/20141163937842500 
07/05/201411541053243654 
08/05/201413641189645018 
09/05/201418871378346905 
10/05/201419051568848810 
11/05/20149691665749779 
12/05/201412591791651038 
13/05/201411391905552177 
14/05/201411042015953281 
15/05/201412522141154533 
16/05/201416652307656198 
17/05/201417882486457986 
27/05/201412486557987month total

 

 

 

 

Need Help

15 Replies

    • bherring1979's avatar
      bherring1979
      Frequent Visitor
      10 years ago

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

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        10 years ago

        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

         

         

  • leonardmurphy's avatar
    leonardmurphy
    Icon for Skilled Sharer rankSkilled Sharer
    10 years ago

    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's avatar
      parry2k
      Icon for Super User rankSuper User
      10 years ago

      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,