Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@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

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.