Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 =
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.
Solved! Go to 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 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
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |