cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BILearner
Advocate I
Advocate I

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 = 
VAR TYQ1Start = STARTOFYEAR(DATESQTD(DATEADD('Calendar'[DateKey],0,YEAR)))
VAR TYQ1END = ENDOFQUARTER(STARTOFYEAR(DATESQTD(DATEADD('Calendar'[DateKey],0,YEAR))))
RETURN
CALCULATE([Total Sales], DATESBETWEEN('Calendar'[DateKey],TYQ1Start,TYQ1END))

Q1 PY Sales as - 

Q1 PY Sales = 
VAR PYQ1Start = STARTOFYEAR(DATESQTD(DATEADD('Calendar'[DateKey],-1,YEAR)))
VAR PYQ1END = ENDOFQUARTER(STARTOFYEAR(DATESQTD(DATEADD('Calendar'[DateKey],-1,YEAR))))
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? 

 

PBI_Dynamic_Quarter.png


Thank you for your help in advance!


 

4 REPLIES 4
Deep21
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

amitchandak
Super User
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. 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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