QTD/YTD DAX

[sujaynadkarni]

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?

[Anonymous]

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

[Cmcmahan]

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.

[sujaynadkarni]

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

Fiscal Calendar

[Anonymous]

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

[sujaynadkarni]

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

[Anonymous · Accepted solution]

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

[sujaynadkarni]

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

Thanks for your help.