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.
NET_SALES QoQ% =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_QUARTER =
CALCULATE(
SUM('research ra_qtr_template'[NET_SALES]),
DATEADD('Calendar'[Date].[Date], -1, QUARTER)
)
RETURN
DIVIDE(
SUM('research ra_qtr_template'[NET_SALES]) - __PREV_QUARTER,
__PREV_QUARTER
)
)
How to acheive QOQ% using Quick measeure.
Can you help me out. How to fix it.
Solved! Go to Solution.
Hi @Anonymous
Assume "research ra_qtr_template" table has a "date" column
you could create such measures
total this quarter = CALCULATE(SUM(Sheet3[net_sales]),FILTER(ALLEXCEPT(Sheet3,Sheet3[code]),[Quarter]=MAX([Quarter]))) PREV_QUARTER = CALCULATE(SUM(Sheet3[net_sales]),FILTER(ALLEXCEPT(Sheet3,Sheet3[code]),[Quarter]=MAX([Quarter])-1)) QOQ% = ([total this quarter]-[PREV_QUARTER])/[PREV_QUARTER]
Also the three measures could put into one measure
QQQ%= var total this quarter = CALCULATE(SUM(Sheet3[net_sales]),FILTER(ALLEXCEPT(Sheet3,Sheet3[code]),[Quarter]=MAX([Quarter]))) var PREV_QUARTER = CALCULATE(SUM(Sheet3[net_sales]),FILTER(ALLEXCEPT(Sheet3,Sheet3[code]),[Quarter]=MAX([Quarter])-1)) Return (total this quarter-PREV_QUARTER) / PREV_QUARTER
The "Quarter" column can be created by going to Edit Queries, then click on the "date" column, then select Transform->Date->Quarter->Quarter of year.
Best Regards
Maggie
Hi @Anonymous
Assume "research ra_qtr_template" table has a "date" column
you could create such measures
total this quarter = CALCULATE(SUM(Sheet3[net_sales]),FILTER(ALLEXCEPT(Sheet3,Sheet3[code]),[Quarter]=MAX([Quarter]))) PREV_QUARTER = CALCULATE(SUM(Sheet3[net_sales]),FILTER(ALLEXCEPT(Sheet3,Sheet3[code]),[Quarter]=MAX([Quarter])-1)) QOQ% = ([total this quarter]-[PREV_QUARTER])/[PREV_QUARTER]
Also the three measures could put into one measure
QQQ%= var total this quarter = CALCULATE(SUM(Sheet3[net_sales]),FILTER(ALLEXCEPT(Sheet3,Sheet3[code]),[Quarter]=MAX([Quarter]))) var PREV_QUARTER = CALCULATE(SUM(Sheet3[net_sales]),FILTER(ALLEXCEPT(Sheet3,Sheet3[code]),[Quarter]=MAX([Quarter])-1)) Return (total this quarter-PREV_QUARTER) / PREV_QUARTER
The "Quarter" column can be created by going to Edit Queries, then click on the "date" column, then select Transform->Date->Quarter->Quarter of year.
Best Regards
Maggie
@v-juanli-msft I attempted to do the same calculation, and then visualize my % change quarter over quarter as a bar graph for quarters 2018 - quarters 2019. The problem is my measures currently show some "Infinity" values for the earliest quarter so I cannot filter the date hierarchy on my original fact table's "date" column down the year+quarter level in the graph. Any advice? I set up a calculated table column (to order my quarters from year to year) and QoQ measure from the following blog post (https://stackoverflow.com/questions/57757128/qoq-measures-in-power-bi).
I need a way to get rid of the infinity values now for graphing purpose, but when I filter out the quarter that contains infinity values in the filters pane, the graph automatically picks the next earliest quarter and makes that an infinity value... any way around this?
Thanks!
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |