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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.