cancel
Showing results 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

## 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

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors