How to calculate the difference between the present and the previous row (present and previous week)

[Anonymous]

Hi everyone,

I'm trying to calculate, but strugling more than i expected, the difference between the the row N and the row N-1 in a virtual table, each row being a year&week concatenation. In other words, i have a table on my report on which i would like to calculate the variation between the value of the week N and it's previous week, like a simple minus calculation.

I tried some ways to do that but i think i'm stuck in terms of context. I know i need to consider the filter context of each row, since i need the formula to see which is the week i am for each row, so it could search for the previous one, but all i got was a full blank column.

Here is what i did:

I made a "week" table on which i have the PresentYearWeek and the PreviousYearWeek. It is as the following

Obs.:

Ano e Semana = Year and Week

Anterior = Previous

Then, i connected this table with my calendar table.

Finally, i created a visual like this:

After several frustating tries to obtain the variation value, i tried first to calculate the previous week value, but failed as well. This is the best i got:

last_week_value =

CALCULATE([measure],

FILTER('D_Calendário','D_Calendário'[Ano e Semana]=RELATED('D_CalendárioSemanas'[Ano e Semana (Anterior)])))

Could please anyone help me with this. I think i'm doing something very wrong because it doesn't looks to be so complex to accomplish.

[Greg_Deckler]

@Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[amitchandak · Accepted solution]

@Anonymous , Actually you can create a rank on Year Week and use that to get prior week

Example

New column

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYMM format

example measure
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Refer my blog

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

Thanks @amitchandak, it worked smothlly and strait forward. Nice approach.