cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
donaldm
Helper III
Helper III

Fiscal quarter to date calculation

Hi everyone,

 

I have a date table with fiscal quarter number defined per our organization.  How can I sum a list of numbers from a separate table according to the fiscal quarters I defined in my calendar table?

 

Many thanks!

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

There should be a relationship from the Date column of the Other Table to the Date column of the Calendar Table.  In your visual, drag the Fiscual Quarters from the Calendar Table.  Write this measure

 

=SUM(Data[Amount])

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

My question is about a way to create a measure to calculate fiscal quarter to date (versus the default QTD)

Hi,

Assuming your fiscal quarter is from April to March, write these calculated column formula in the Calendar Table.

Fiscal Year = IF(MONTH(Calendar[Date])<=3,YEAR(Calendar[Date])-1&"-"&YEAR(Calendar[Date]),YEAR(Calendar[Date])&"-"&YEAR(Calendar[Date])+1)

Month = FORMAT(Calendar[Date],"mmmm")

In your visual, drag Fiscal Year and Month from the Calendar Table.  Write these measures

Revenue = SUM(Data[Amount])

QTD Revenue = CALCULATE([Revenue],DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-2),MAX(Calendar[Date])))

Hope this helps.

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for the response.  What does "-2" mean in your formula:

 

QTD Revenue = CALCULATE([Revenue],DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-2),MAX(Calendar[Date])))

 

Also, using the concept of your formula above, what is the formula to calculate the value of Previous Fiscal Quarter?

You are welcome.  -2 means "Go back 2 months".  Does this give the correct result?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!