Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a measure that was created via "New Quick Measure" to calculate the QoQ% Change based on a calculated column (just the total of column A + B + C).
I'd like to plot these column and the QoQ% Change into a bar and line chart, and add a Date Slicer. However, it seems that the measure cannot be filtered and displayed by the chart when Date Slicer is being adjusted and causes the whole chart to show error.
[Full date period]
[Adjusted Date period]
As I am new to Power BI, I wonder if anyone can shed some light to what might be the solution for this. I believe it is doable to be done in Power BI, but perhaps there is a trick to make it happen...
Best Regards,
Owen
Hi @owenkosnen
You can use the following measures:
QTD = CALCULATE ( SUM ( Table[Amount] ) , DATESQTD ( 'Date'[Date] ) )
Last QTR = CALCULATE ( SUM ( Table[Amount] ) , DATEADD ( 'Date'[Date] , -1 , Quarter ) )
QoQ% = ( [QTD] - [Last QTR] ) / [Last QTR]
Ensure to change the measure [QoQ%] to percentage.
Hope this helps.
Theo 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@owenkosnen sorry, I had an extra bracket at the end of the QoQ%
QoQ% = ( [QTD] - [Last QTR] ) / [Last QTR]
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Yes I also noticed the extra bracket there but seems it is the issue...
I am ]curious since my base column for (Table[Amount]) is actually a calculated column, could it be the reason why it is not working ?
@owenkosnen it is likely to do with the relationship you have. Have you established a relationship between the Date table and Fact table. Have you got a Date column in both the Date table and Fact table?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@TheoC there is only one source table for all data so maybe we can opt out relationship setups.. (though I just learned that the best practice should be to keep Date Table separated)
@owenkosnen how are the dates presented in your table? You will need to change the 'Date'[Date] reference in the measures I recommended to your TableName and [Date] column in order for them to work, subject to you have an appropriate Date column.
Hope that makes sense.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi Theo,
Thanks a lot for your quick response!
I tried your suggestion to create these measures but not sure why I get Infinity as the value for QoQ%. I have made sure to change it to Percentage as well.
@owenkosnen sorry, I had an extra bracket at the end of the QoQ%
QoQ% = ( [QTD] - [Last QTR] ) / [Last QTR]
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@owenkosnen , Update QoQ% like below:-
QoQ% = divide( ([QTD] - [Last QTR] ), [Last QTR] ,0)
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hello,
I tried your formula but it is not working unfortunately..
I just updated my post with a screenshot for more info, the QoQ is generated with New Quick Measure but just not working with Date Slicer
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |