cancel
Showing results for
Did you mean:

## Dynamic Quarterly Sales Calculation - Q1TY , Q1PY , Q2TY , Q2 PY etc.

Hello PBI Community,

I am trying to write dynamic Quarterly Sales calculation that will respect user selection in the date slicer. I am NOT looking to hardcode Year and quarter values in the measure as this gets tedious to manage when year changes.

For instance, I want to compute Q1 TY (Quater 1 ThisYear sales), Q1 PY (Quater 1 PreviousYear sales), Q2 TY, Q2 PY, Q3 TY, Q3 PY,Q4 TY, Q4 PY

How can I acheive this ? So far I have

Q1 TY Sales as -

``````Q1 TY Sales =
RETURN
CALCULATE([Total Sales], DATESBETWEEN('Calendar'[DateKey],TYQ1Start,TYQ1END))``````

Q1 PY Sales as -

``````Q1 PY Sales =
RETURN
CALCULATE([Total Sales], DATESBETWEEN('Calendar'[DateKey],PYQ1Start,PYQ1END))``````

How can these measures be replicated for Q2,Q3,Q4?
Is this the correct way to write these measures? Or there's a better way  to do it?
I am struggling to find QuarterStartDate and QuarterEndDates to use in variables for calculation of Q2,Q3,Q4 measures.

The current measures I have written replicates values of that measure across other quarters too. Is there a way to show Q1 values for only Q1, Q2 for only Q2 etc?

4 REPLIES 4
Frequent Visitor

@BILearner - hye did you find any solution for your query ? I have to implement same thing and i am stuck being new to Power bi

Super User

@BILearner , I think you need measure like

Q1= CALCULATE([Total Sales], filter('Calendar',Quarter('Calendar'[DateKey])=1))

Q2= CALCULATE([Total Sales], filter('Calendar',Quarter('Calendar'[DateKey])=2))

Thank you for your inputs @amitchandak , unfortunately that solution won't work for me. Because the solution I building is going to be a self service dataset. User's can use these metics against any other dimension to build their own reports.

For example if I use above calculations against Product Name attribute it will not show me correct results.
The measure Q1= CALCULATE([Total Sales], filter('Calendar',Quarter('Calendar'[DateKey])=1)) will display a total of all possible Q1's.

Community Support

Hi, @BILearner

Not sure what you want. Can you share a sample file for further research.

Please check if the formula below could help:

``````Q1 =
CALCULATE (
[Total Sales],
FILTER (
'Calendar',
QUARTER ( 'Calendar'[DateKey] ) = QUARTER ( EOMONTH ( TODAY (), 0 ) )
)
)
``````

Best Regards,
Community Support Team _ Eason

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors