topic Re: Issues in MTD/QTD/YTD standard measures in DAX Commands and Tips

Issues in MTD/QTD/YTD standard measures

VikramShah — Mon, 17 May 2021 15:09:40 GMT

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])

Re: Issues in MTD/QTD/YTD standard measures

Greg_Deckler — Mon, 17 May 2021 15:13:54 GMT

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-TITHW/m-p/434008

Re: Issues in MTD/QTD/YTD standard measures

VikramShah — Mon, 17 May 2021 18:30:57 GMT

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

Re: Issues in MTD/QTD/YTD standard measures

Anonymous — Mon, 17 May 2021 20:25:54 GMT

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 !

Re: Issues in MTD/QTD/YTD standard measures

VikramShah — Tue, 18 May 2021 04:37:49 GMT

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

Re: Issues in MTD/QTD/YTD standard measures

Anonymous — Tue, 18 May 2021 05:43:33 GMT

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.