Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Error calculating QOQ% using quick measure

  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."...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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!