Error calculating QOQ% using quick measure

[Anonymous]

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.

[v-juanli-msft · Accepted 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

[Anonymous]

@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!