Issues in MTD/QTD/YTD standard measures

[VikramShah]

Hi,

I have a very simple data wherein there are only 3 columns Date/Segment/Values, however there is no data for Defense Category in the month of April. But when I use the MTD/QTD/YTD standard measures Power Bi shows me data from earlier Month (for MTD), earlier Quarter (for QTD) and earlier Year (For YTD). What I want to know is that, is there a bug in the standard MTD/QTD/YTD queires or am I doing something wrong. I have attached the excel snapshot along with Power Bi table image where there is simple SUM used and other image showing Power Bi table where I have used MTD/QTD queries. Also I noted that the Defense values are not added in the Total.

I am also attaching the quries below:

MTD = TOTALMTD(SUM(Sheet1[Value]),Sheet1[Date])

QTD = TOTALQTD(SUM(Sheet1[Value]),Sheet1[Date])

[mah_priya94]

Hi @VikramShah ,

Would suggest you create a Calendar Table using the following DAX :

Date = CALENDAR(MIN(Table_2[Column1]), MAX(Table_2[Column1]))

Link this Calendar table to the main table 

Then make the following changes in your existing MTD and QTD DAX measures:

MTD = TOTALMTD(SUM(Table_2[Value]), 'Date'[Date])

QTD = TOTALQTD(SUM(Table_2[Value]), 'Date'[Date])

I used the following data as a sample:

and got the results !

[VikramShah]

@mah_priya94thanks for the reply, but still it is giving me the same results.

You have put the months in Columns in the matrix. Kindly remove the months from coulmn and check the result. I am doing so because eventually I am going to make a pie chart of this.

[mah_priya94 · Accepted solution]

Hi @VikramShah ,

Even after removal of Months I am getting correct results. 
I added a record to the existing sample.

SInce MTD and YTD are DAte functions, latest date (Month and Quarter) is considered in order to evaluate MTD and QTD since no date elements are given to provide the division.

[Greg_Deckler]

@VikramShah You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[VikramShah]

@Greg_DecklerI am getting the same result using the DAX from your first link