Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
For a table that shows values per week, I would like to add a column with the values of the previous week, so I can calculate a week-on-week change, and eventually a count of wins (positive changes) and losses (negative changes). I wanted to use a DAX formula with PARALLELPERIOD. However this is only possible for Years, Months and Days and not for Weeks.
Does anyone know the solution or a workaround to get the same result?
Thanks.
Kind regards,
Evelien
Solved! Go to Solution.
Hi Evelien,
Try this formula please. It's a calculated column.
NewColumn = VAR LastWeek = CALCULATE ( MAX ( 'Table1'[Date] ), FILTER ( 'Table1', 'Table1'[Date] < EARLIER ( 'Table1'[Date] ) && 'Table1'[App] = EARLIER ( Table1[App] ) && 'Table1'[Keyword] = EARLIER ( 'Table1'[Keyword] ) ) ) RETURN CALCULATE ( MIN ( 'Table1'[Rank] ), FILTER ( 'Table1', 'Table1'[Date] = LastWeek && 'Table1'[App] = EARLIER ( Table1[App] ) && 'Table1'[Keyword] = EARLIER ( Table1[Keyword] ) ) )
Best Regards!
Dale
Hi,
How does your table look like? If you had one like it in the picture, you could have two options to get it done.
Option 1: If your table had a column WeekNum, you can sort the table with WeekNum. If not, you can add an index as WeekNum. First sort the table by date, then add an index. Finally, you can use this formula as a calculated column.
Option_1 = CALCULATE ( MIN ( Table1[Amount] ), FILTER ( 'Table1', 'Table1'[Weeknum] = EARLIER ( 'Table1'[Weeknum] ) - 1 ) )
Option 2: If you used a date to act as a week, you could use this formula as a calculated column.
Option_2 = VAR LastWeek = CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) ) ) RETURN CALCULATE ( MIN ( Table1[Amount] ), FILTER ( Table1, Table1[Date] = LastWeek ) )
Please don’t worry about Min function. Actually there is only one value when we use Min due to running context.
Best Regards!
Dale
Thanks for the suggestions. This seems to be what I am looking for.
However, something I forgot to mention, I would need to know the last week value not only by date, but also by 2 other parameters: Keyword and App.
Please find below the table to see what I mean and what I am after:
Keyword | Date | App | Rank | Rank_Last_Week |
Keyword 1 | 8/05/2017 | App 1 | 100 | |
Keyword 2 | 8/05/2017 | App 1 | 200 | |
Keyword 3 | 8/05/2017 | App 1 | 300 | |
Keyword 1 | 15/05/2017 | App 1 | 400 | 100 |
Keyword 2 | 15/05/2017 | App 1 | 500 | 200 |
Keyword 3 | 15/05/2017 | App 1 | 600 | 300 |
Keyword 1 | 22/05/2017 | App 1 | 700 | 400 |
Keyword 2 | 22/05/2017 | App 1 | 800 | 500 |
Keyword 3 | 22/05/2017 | App 1 | 900 | 600 |
Keyword 1 | 8/05/2017 | App 2 | 150 | |
Keyword 2 | 8/05/2017 | App 2 | 250 | |
Keyword 3 | 8/05/2017 | App 2 | 350 | |
Keyword 1 | 15/05/2017 | App 2 | 450 | 150 |
Keyword 2 | 15/05/2017 | App 2 | 550 | 250 |
Keyword 3 | 15/05/2017 | App 2 | 650 | 350 |
Keyword 1 | 22/05/2017 | App 2 | 750 | 450 |
Keyword 2 | 22/05/2017 | App 2 | 850 | 550 |
Keyword 3 | 22/05/2017 | App 2 | 950 | 650 |
I hope it is clear.
Do you think in some way it will be possible to adjust your proposed formula to this?
Many thanks for your help!
Kind regards,
Evelien
Hi Evelien,
Try this formula please. It's a calculated column.
NewColumn = VAR LastWeek = CALCULATE ( MAX ( 'Table1'[Date] ), FILTER ( 'Table1', 'Table1'[Date] < EARLIER ( 'Table1'[Date] ) && 'Table1'[App] = EARLIER ( Table1[App] ) && 'Table1'[Keyword] = EARLIER ( 'Table1'[Keyword] ) ) ) RETURN CALCULATE ( MIN ( 'Table1'[Rank] ), FILTER ( 'Table1', 'Table1'[Date] = LastWeek && 'Table1'[App] = EARLIER ( Table1[App] ) && 'Table1'[Keyword] = EARLIER ( Table1[Keyword] ) ) )
Best Regards!
Dale
Hi Evelien,
It's my pleasure.
Best Regards!
Dale
not sure if this helps at all https://community.powerbi.com/t5/Desktop/Create-Previous-Week-Filter/td-p/9492
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |