topic Re: QTD/YTD DAX in DAX Commands and Tips

QTD/YTD DAX

sujaynadkarni — Mon, 29 Jul 2019 19:28:24 GMT

Hello Everyone,

I apologize if this question has been asked before. But, I am unable to figure out why my QTD DAX formula would not show correct quantity. To provide some context, our organization has a custom fiscal calendar that starts on October 1st. I wrote the following DAX command:

Quantity QTD = CALCULATE([Total Quantity],DATESQTD('Fiscal Calendar'[CalendarDate]))

and see that one record is missing from the table. This record was approved on 06/29, which happens to be the first day of current quarter, but powerbi does not recognize it as this quarter. Any ideas how I can fix that?

Re: QTD/YTD DAX

Cmcmahan — Mon, 29 Jul 2019 19:51:52 GMT

When you say that your company has a customized fiscal calendar, do you mean that you have a custom date table in PowerBI, or just that your company generally has a fiscal calendar starting Oct1?

Is PowerBI aware that 6/29 is in the current fiscal quarter? If you could get PowerBI to list out all dates in the current quarter, you can make sure that it recognizes 6/29 properly. Once you're sure of that, we can dig into why it might be failing for the first day of the quarter.

Re: QTD/YTD DAX

Anonymous — Mon, 29 Jul 2019 21:14:17 GMT

Well, mate, you're out of luck. Almost all time-intel functions DO NOT WORK with custom calendars. You have to write your own logic (but it's not too hard if you know DAX a bit) for the non-standard calendar.

Sorry.

Best

Darek

Re: QTD/YTD DAX

sujaynadkarni — Wed, 31 Jul 2019 17:11:34 GMT

Hi,

Just generatlly that our company has a fiscal calendar starting october 1st. I see that PowerBI is aware that 06/29 is the current fiscal quarter. See below

Re: QTD/YTD DAX

Anonymous — Wed, 31 Jul 2019 17:14:32 GMT

Well, there are a very tiny handful of functions where you can specify the end of the year but they are few and in most of the time-intel functions you cannot specify the date/month in which the year ends.

Best
Darek

Re: QTD/YTD DAX

sujaynadkarni — Wed, 31 Jul 2019 17:35:29 GMT

Darek -

Thanks. YTD function works fine as I was able to specify end date of fiscal year, which is always 09/30. QTD is tricky as Powerbi is using 1st July as the starting date. If I could only specify the start date ;)

In one of your replies, you mentioned that I could write DAX functions. If handy and possible, could you point me to the resources. I am new to powerbi - 2 weeks old and happy to learn. Thanks for your help

Re: QTD/YTD DAX

Anonymous — Wed, 31 Jul 2019 17:37:46 GMT

Mate... a long, long way ahead of you :)

Please go to www.sqlbi.com and search for articles on "custom calendars." You'll find the techniques that will enable you to tackle any kind of calendar you want. But be prepared to sweat.

Best
Darek

Re: QTD/YTD DAX

sujaynadkarni — Wed, 31 Jul 2019 18:07:11 GMT

I already am. But, this is good sweat - releasing endorphins. lol.

Thanks for your help.