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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
devanshi_ag
Regular Visitor

How to show QoQ change dynamically basis on slicer selection

Hi I want to show QoQ change using a line chart (with year and qtr as x axis and change in % as y axis) which calculates change basis on user selection. 
For example-
If the user selects Q2 and Q4 from the slicer it calculates the change between these two quarters and if he/she selects Q1, Q2, Q4 it shows change as per that?

The quick measure in power Bi takes only previous not the selected quarters. Can someone help with the dax code for this?

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@devanshi_ag , Make sure you are using a date table joined with date of you table and you can have last qtr meausre using

 

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

or

 

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])))

 

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


Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Anonymous
Not applicable

Hi,

Thanks for the solution @amitchandak  providedm, and i want to offer some more infotmtion for user to refer to.

hello @devanshi_ag , you can refer to the following solution.

Sample data

vxinruzhumsft_0-1718606047666.png

 

1.Create a calendar table and create a 1:n relationship between tables.

vxinruzhumsft_1-1718606089737.png

 

2.Create the follwing measures

Sum = SUM('Table'[Value])
QTQ% =
VAR a =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[QuarterNo] )
    )
VAR b =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] < a )
    )
VAR c =
    CALCULATE (
        [Sum],
        ALLEXCEPT ( 'Table', 'Calendar'[Date].[Year], 'Calendar'[Date].[QuarterNo] ),
        YEAR ( 'Calendar'[Date] ) = YEAR ( b ),
        QUARTER ( 'Calendar'[Date] ) = QUARTER ( b )
    )
RETURN
    DIVIDE ( [Sum] - c, c )

3.Then put the following field to the line chart visual.

vxinruzhumsft_2-1718606199526.png

 

Output

vxinruzhumsft_3-1718606222125.png

 

Best Regards!

Yolo Zhu

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi,

Thanks for the solution @amitchandak  providedm, and i want to offer some more infotmtion for user to refer to.

hello @devanshi_ag , you can refer to the following solution.

Sample data

vxinruzhumsft_0-1718606047666.png

 

1.Create a calendar table and create a 1:n relationship between tables.

vxinruzhumsft_1-1718606089737.png

 

2.Create the follwing measures

Sum = SUM('Table'[Value])
QTQ% =
VAR a =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[QuarterNo] )
    )
VAR b =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] < a )
    )
VAR c =
    CALCULATE (
        [Sum],
        ALLEXCEPT ( 'Table', 'Calendar'[Date].[Year], 'Calendar'[Date].[QuarterNo] ),
        YEAR ( 'Calendar'[Date] ) = YEAR ( b ),
        QUARTER ( 'Calendar'[Date] ) = QUARTER ( b )
    )
RETURN
    DIVIDE ( [Sum] - c, c )

3.Then put the following field to the line chart visual.

vxinruzhumsft_2-1718606199526.png

 

Output

vxinruzhumsft_3-1718606222125.png

 

Best Regards!

Yolo Zhu

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

amitchandak
Super User
Super User

@devanshi_ag , Make sure you are using a date table joined with date of you table and you can have last qtr meausre using

 

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

or

 

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])))

 

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


Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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