Row context to calculate WoW, YoY, PoP

Question & Answer
1

Hi All,

does anyone know how to get the current row context in a visual, to calculate, for a specific row, the WoW or YoY or PoP?

Eg. (table I have, exactly this, so imagine it with one row)
Week | CY Metric | LY Metric | YoY as a difference
2021-40 | 50% | (how to calculate this?) | (how to calculate this?)

So in this example, I should say to calculate LY Metric or YoY:
LY = sum( ifelse( {year}={year}-1, Metric Value, 0 ) )

As you can imagine, the top formula is not working due to the row context that is related only to week 2021-40 and year=2021. I’ve tried with sumOver and PRE_FILTER to avoid the row context, but in that case, I’m not able to get the current year so 2021 to compute {year}-1 in the formula.

In power bi I will do in this way:
VAR SELECTED_YEAR = SELECTEDVALUE(YEAR)
CALCULATE( SUM(METRIC), ALL(YEAR), YEAR=SELECTED_YEAR-1 )

Thanks!

1 Like
2

This solution is a little more complex than just doing a CY, LY, YoY, etc without including the actual periods in your visual, but is achievable. You can use a table calculation to look up the value X rows before or after using LEAD() or LAG(). Then you can do math between those two fields (CY and LY) to get your period over period.

First off, CY is just the actual metric - you don’t need a calculated field for that one. Im going to call it ‘Metric’ for this example.

LY would look like:
lag(sum(Metric), [Date ASC], 52)

YoY would be:
sum(Metric) - LY

Now the issue with using Table Calculations is they require the other rows to be in the visual (or at least the query) to be able to reference them. This means you are probably seeing a whole bunch of blank LY and YoY values in the rows of your first year.
blank rows

We can ‘hide’ these rather than ‘filter’ them out of the query by writing one more calc that is another Table Calculation, and adding the filter on that. This works because filters placed on Table Calculations happen all the way at the end of the pipeline, after the query has already been run. Therefore we are not really filtering the data asked for in the query, rather purely filtering the display in the visual (hence why I call it a ‘hide’).

Make another calc called ‘Rank Filter’
rank([min(Date) DESC])

The result of this is just going to be a number from 1 to N, with 1 being the most recent week. When you add a filter on this field (for instance <=52) it will ‘hide’ all the older weeks and just leave the weeks from the current year.

Note: My data goes up to Dec 2020, so Rank=1 is the week of Dec 27, 2020.

See this screenshot of the final result. Does that address your question?

Note: You do not need to include the Rank Filter field in the visual, just showing it here for demo purposes.
Note: You can sort the visual however you want. I have left it with the default sort (old to new), but you can sort it so most recent week is at the top.

PoP
PoP1059×679 61.3 KB

2 Likes
3

Amazing @Jesse , super clear really. Thank you.
I see only one drawback here, if you don’t have an ordered list of period (eg. W1,W2 and then W6) or, more probably, an increasing number of loaded weeks (eg. first update W1,W2… second update W1,W2,W3…etc…) the number on lag function or filtering the rank need to be dynamic for YoY.

Eg.

FIRST SITUATION
2021-W2 —> to get YoY here, I should use instead of “52” in the lag function, the number “3”. As well as for filtering the rank field.
2021-W1 —> hide
2020-W2 —> hide

NEXT UPDATE
2021-W3 —> to get YoY here, I should use instead of “52” in the lag function, the number “4”. As well as for filtering the rank field. (new)
2021-W2 —> hide
2021-W1 —> hide
2020-W3 —> hide (new)
2020-W2 —> hide

Is there a way to automated this by, for eg., a dynamic parameter or another calculated fields?
Changing manually the number on lag function or on rank filtering, would be the same as using a manually inputted week into the formula.

I’m opened to any kind of solution, also changing the dataset design.

Thanks,

4

Another approach that you can also try other than Table Calculation if you would like to incorporate Parameters to drive the dynamic calculations - this is an example that I have to drive YTD Sales comparing CY and PY:

  1. Outcome:
    image

  2. Steps:

  • YTD Sales: Check if the date falls in the Current Year period. I’m using a year parameter ${yearselection1} to drive the current year selection in my example. You can change it to now() to check against the latest date of the dashboard.
ifelse(extract("YYYY",{Order Date})=${yearselection1}, Sales,0)
  • YTD PY Sales: Check if the date falls in the Previous Year period
ifelse(extract("YYYY",{Order Date})=${yearselection1}-1, Sales,0)
  • Filter Month YTD:Check if the month falls in the YTD period and add this as a filter to include data = 1
ifelse(extract("MM",{Order Date})<=extract("MM",now()),1,0)
  • YTD Comparison: Calculate YTD difference
(sum({YTD Sales})-sum({YTD PY Sales}))/sum({YTD PY Sales})
5

Hi @seichyi, thanks for this.

This is what I’m trying to do, but

  1. I don’t want to use static parameters like your ${yearselection1} (I want the value in this parameter automatically recognized)
  2. I cannot use now() function, instead I would like to use the max(string) function that seems not exists (here the issue Max of String (sorted in alphabetical order) in a calculated field - #4 by robdhondt)
  3. I would like to use your logic, but with a dimension that is filtering the dates (ex 2021-40 is filtering the year to 2021), and use the year field dynamically so getting, for eg., {year}-1 ignoring the “indirect” filter on the year. (indirect because I used year_week in the visual).

Thanks,

6

Hi @Andrea,

Thanks for the further description.
If that’s the case, I think you would need Level Aware Aggregations (LAA) here.

The easiest way that I can think of right now:

  1. Extract just the week out from your “Week” column using split or right function - namely “Week Number”
  2. Get the latest year in your dataset, namely “Get the latest year in the dataset”:
extract("YYYY",maxOver({Order Date - New},[],PRE_AGG))
  1. Compute sales for current year, namely “Weekly Sales CY”:
ifelse(extract("YYYY",{Order Date - New})={10.0 Get the latest year in the dataset},Sales,0)
  1. Compute sales for previous year, namely “Weekly Sales PY”:
ifelse(extract("YYYY",{Order Date - New})={10.0 Get the latest year in the dataset}-1,Sales,0)
  1. Compute YoY Difference:
(sum({10.2 Weekly Sales CY})-sum({10.3 Weekly Sales PY}))/sum({10.3 Weekly Sales PY})

Outcome:

image
image790×518 42.8 KB

To understand more about LAA:

1 Like