cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

fraletti_algar_0-1610104015488.png

Obs.:

Ano e Semana = Year and Week

Anterior = Previous

 

Then, i connected this table with my calendar table.

fraletti_algar_1-1610104114035.png

 

Finally, i created a visual like this:

fraletti_algar_2-1610104176324.png

 

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
amitchandak
Super User IV
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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User IV
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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User IV
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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Anonymous
Not applicable

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.