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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

QnQ trend for last 4 years in different way

Hi Team, 

I have one requirement in which need to display sum of sales for last 5 years of selected quarter. eg: 
if I select 2022 Q1 than it should display the trend of 2021 Q1, 2020 Q1, 2019 Q1, 2018 Q1 and so on. similarly for other selections as well. 

I have tried with samePeriodlastyear or parallalperiod but they all are limited to 1 year or may be other as well but comparison will happen only between 2 attribute but my Requirement is to display last 5 years quarters based on my selection in slicer. 

 

Any help would be appreciated.

 

Thanks 

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

I solved a similar problem before, you can refer to it, Solved: Show 13 months for a chosen year in a diagram - Microsoft Power BI Community

For questions about quarter, you can try this,

(1) create dimdate and slicer table

vxiaotang_1-1652172012182.png

(2) create the measure

Measure = 
var _selectedYQ=SELECTEDVALUE(Slicer[YQ])
var _selectedY=SELECTEDVALUE(Slicer[Y])
var _selectedQ=SELECTEDVALUE(Slicer[Q])
var _startY=_selectedY-4
var _sumPerYQ=CALCULATE(SUM(FactTable[value]),FILTER(ALL(FactTable),Quarter(FactTable[date])=MIN(DimDate[Quarter]) && YEAR(FactTable[date])= MIN(DimDate[Year])))
return IF(MIN(DimDate[Year])>=_startY && MIN(DimDate[Year])<=_selectedY && MIN(DimDate[Quarter])=_selectedQ,_sumPerYQ,BLANK())

result

vxiaotang_2-1652172247030.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

I solved a similar problem before, you can refer to it, Solved: Show 13 months for a chosen year in a diagram - Microsoft Power BI Community

For questions about quarter, you can try this,

(1) create dimdate and slicer table

vxiaotang_1-1652172012182.png

(2) create the measure

Measure = 
var _selectedYQ=SELECTEDVALUE(Slicer[YQ])
var _selectedY=SELECTEDVALUE(Slicer[Y])
var _selectedQ=SELECTEDVALUE(Slicer[Q])
var _startY=_selectedY-4
var _sumPerYQ=CALCULATE(SUM(FactTable[value]),FILTER(ALL(FactTable),Quarter(FactTable[date])=MIN(DimDate[Quarter]) && YEAR(FactTable[date])= MIN(DimDate[Year])))
return IF(MIN(DimDate[Year])>=_startY && MIN(DimDate[Year])<=_selectedY && MIN(DimDate[Quarter])=_selectedQ,_sumPerYQ,BLANK())

result

vxiaotang_2-1652172247030.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

tamerj1
Super User
Super User

Hi @Anonymous 

you can try something like 

CALCULATE (

[Measure],
Date[Year] <= MAX ( Date[Year] ),

Date[Year] >= MAX ( Date[Year] ) - 5

)

Anonymous
Not applicable

Hi @tamerj1 , 

 

Thanks for your response on this. Actually My requirment is to display data of same quarter previous years (eg: if I select 2021 q4 then it should display 2020 q4, 2019 q4,2018 q4......) with solution provided above , not able to achieve this. May be if you can explain more in details than it would be helpful for me. 

 

Thanks in advance. 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors