cancel
Showing results for
Did you mean:
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
Super User III

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
Helper III

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

Super User III

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
Helper III

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?

Super User III

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors