The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
@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
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
1.Create a calendar table and create a 1:n relationship between tables.
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.
Output
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.
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
1.Create a calendar table and create a 1:n relationship between tables.
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.
Output
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.
@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