cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sujaynadkarni
Frequent Visitor

QTD/YTD DAX

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?
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Resident Rockstar
Resident Rockstar

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.

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 CalendarFiscal Calendar

Anonymous
Not applicable

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

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
Not applicable

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

View solution in original post

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

 

Thanks for your help.  

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors