Forum Discussion
Calculate weekly variation using date slicer
Hello!
I have a table with weekly data, with the weekly balance for several clients (calculated as a measure 'Total' which sums various components).
How can I calculate the weekly variation, using a date slicer to select the date reference? So if I select 14/08/2023 (dd/mm/yyyy), it would calculate the variation of the Total measure with reference to 7/08/2023, if I select 7/08/2023 it would calculate the variation to 31/07/2023, etc.
Here is a simplified sample of the table (named Sheet1):
| Date | ID | Value 1 | Value 2 |
| 31-Jul | cnpt1 | 100 | 213 |
| 31-Jul | cnpt2 | 200 | 3243 |
| 31-Jul | cnpt3 | 300 | 123 |
| 07-Aug | cnpt1 | 123 | 12 |
| 07-Aug | cnpt2 | 234 | 35 |
| 07-Aug | cnpt3 | 54 | 65 |
| 14-Aug | cnpt1 | 12345 | 8 |
| 14-Aug | cnpt2 | 123 | 787 |
| 14-Aug | cnpt3 | 13134 | 56 |
The Total is calculated as a measure that sums Value 1 and Value 2. I tried to calculate it by having a slicer where the date is chosen and calculate a measure as follows:
But it doesn't return anything because of the PreviousDate variable (it seems to be blank).
Any suggestions on how to calculate the weekly variation?
Thank you!
Thank you for your reply! This didn't work for me because I wanted to calculate the variation individually (for each counterparty).
I'm leaving the solution that worked for me here, if it helps someone else:
Variation_Weekly =VAR SelectedDate = SELECTEDVALUE(Sheet1[Date])VAR PreviousDate =CALCULATE(MAX(Sheet1[Date]),FILTER(ALL(Sheet1),Sheet1[Date] < SelectedDate))VAR PreviousValue =CALCULATE([Total],Sheet1[Date] = PreviousDate)RETURN[Total] - PreviousValue
4 Replies
- amitchandak
Super User
dfgs , Try like
Variation_Week =
VAR CurrentDate = Maxx(allselected(Sheet1) , Sheet1[Date])
VAR PreviousDate = CALCULATE(MAX(Sheet1[Date]), FILTER(allselected(Sheet1) , Sheet1[Date] < CurrentDate))
RETURN
SUMX(FILTER(all(Sheet1[Date]), Sheet1[Date] = CurrentDate),[total])
- SUMX(FILTER(all(Sheet1[Date]), Sheet1[Date] = PreviousDate), [total])But the best way is to use a date table and try WOW
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=pnAesWxYgJ8Thank you for your help! I tried this but it was calculating the total of the week selected, not the variation.
I'm leaving the solution that worked for me here, if it helps someone else:
Variation_Weekly =VAR SelectedDate = SELECTEDVALUE(Sheet1[Date])VAR PreviousDate =CALCULATE(MAX(Sheet1[Date]),FILTER(ALL(Sheet1),Sheet1[Date] < SelectedDate))VAR PreviousValue =CALCULATE([Total],Sheet1[Date] = PreviousDate)RETURN[Total] - PreviousValue
Hi dfgs ,
Here are the steps you can follow：
1. Create measure.
Measure = var _select=SELECTEDVALUE('Table'[Date]) var _current= SUMX( FILTER(ALL('Table'),'Table'[Date]=_select),[Value 1]+[Value 2]) var _lastmindate= MINX( FILTER(ALL('Table'), WEEKNUM('Table'[Date],2)=WEEKNUM(_select,2)-1),[Date]) var _last= SUMX( FILTER(ALL('Table'),'Table'[Date]=_lastmindate),[Value 1]+[Value 2]) return IF( _last=BLANK(),_current-0,_current -_last)2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you for your reply! This didn't work for me because I wanted to calculate the variation individually (for each counterparty).
I'm leaving the solution that worked for me here, if it helps someone else:
Variation_Weekly =VAR SelectedDate = SELECTEDVALUE(Sheet1[Date])VAR PreviousDate =CALCULATE(MAX(Sheet1[Date]),FILTER(ALL(Sheet1),Sheet1[Date] < SelectedDate))VAR PreviousValue =CALCULATE([Total],Sheet1[Date] = PreviousDate)RETURN[Total] - PreviousValue
