Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

 

Thanks for your help.  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors