cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rahulrackup
Microsoft
Microsoft

Calculating current & prior year quarter measure dynamically with separate slicer for month and year

Hi Everyone,

I'm very new to Power BI, appreciate it if you could help me find the solution. My scenario is I need to calculate the total sum of a measure for the current and previous year quarter, please see below snip but the problem is there are two separate slicers one for FISCAL MONTH and other for YEAR. So the requirement is when a user selects any month and year in the slicer the total sum would be the sum for the quarter to which the month falls to.

 

rahulrackup_0-1634535693743.png

 

For example if i selct Month( July), Year 2022, i should get values for first quarter(i.e. July, Aug, Sep) of 2022 and 2021 under column name QTD and QTD PY. below is the formula i have used.

 

QTD- CALCULATE ( SUM ( Data[Value] ), PARALLELPERIOD ( 'Calendar'[Fiscal yearDate], 0, QUARTER ) )

QTD PY-  CALCULATE ( SUM ( Data[Value] ), PARALLELPERIOD ( 'Calendar'[Fiscal yearDate], -1, QUARTER ) )

But numbers are not coming up for QTD-PY. Please help to fix this up

 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@rahulrackup , Hope you have date table , with time intelligence

 

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

 

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

 

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

2 REPLIES 2
rahulrackup
Microsoft
Microsoft

Hi Amit,

Thanks for your response but for previous year quarter it is not working. please see below snip.

rahulrackup_0-1634566326386.png

 

 

For example if i select year 2022 and month Jul, i should be able to see current and previous year quartern i.e. ( Jul, Aug and Sep) quarter value for 2022 and 2021.below formula is not working for QTD PY

 

QTD PY- CALCULATE ( SUM ( Data[QTY] ), PARALLELPERIOD ( 'Calendar'[Date], -1, QUARTER ) )

amitchandak
Super User
Super User

@rahulrackup , Hope you have date table , with time intelligence

 

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

 

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

 

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.