cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Super User IV

@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

Proud to be a Super User!

3 REPLIES 3
Super User IV

@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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User IV

@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

Proud to be a Super User!

Anonymous
Not applicable

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

Announcements