Forum Discussion
Calculate previous week values
Hello ,
I have the following table in Power Bi :
| Movement type | Calendar week | Actual | PreviousWeek |
| Net Growth | 202452 | 50 | |
| Net Disconnections | 202452 | 750 | |
| Net Connections | 202452 | 800 | |
| Net Growth | 202501 | 300 | 50 |
| Net Disconnections | 202501 | 200 | 750 |
| Net Connections | 202501 | 500 | 800 |
| Net Growth | 202502 | 100 | 300 |
| Net Disconnections | 202502 | 900 | 200 |
| Net Connections | 202502 | 1000 | 500 |
I need to find the correct formula to get the last column 'Previous Week' that is the actual value of each single movement type related to the previous week.
Thanks for the help,
Romina
Hi romina80,
You can use below DAX logic to create new Calculated column
PreviousWeek = VAR CurrentMovement = 'Movements'[Movement type] VAR CurrentWeek = 'Movements'[Calendar week] VAR PreviousWeekNumber = CALCULATE(MAX('Movements'[Calendar week]), FILTER('Movements', 'Movements'[Calendar week] < CurrentWeek)) RETURN CALCULATE(MAX('Movements'[Actual]), FILTER('Movements', 'Movements'[Movement type] = CurrentMovement && 'Movements'[Calendar week] = PreviousWeekNumber ))Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.
4 Replies
have you got a date tabel ?
Hi romina80,
You can use below DAX logic to create new Calculated column
PreviousWeek = VAR CurrentMovement = 'Movements'[Movement type] VAR CurrentWeek = 'Movements'[Calendar week] VAR PreviousWeekNumber = CALCULATE(MAX('Movements'[Calendar week]), FILTER('Movements', 'Movements'[Calendar week] < CurrentWeek)) RETURN CALCULATE(MAX('Movements'[Actual]), FILTER('Movements', 'Movements'[Movement type] = CurrentMovement && 'Movements'[Calendar week] = PreviousWeekNumber ))Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.Hi,
This calculated column formula works
=LOOKUPVALUE(Data[Actual],[Calendar week],CALCULATE(MAX(Data[Calendar week]),FILTER(Data,Data[Movement type]=EARLIER(Data[Movement type])&&Data[Calendar week]<EARLIER(Data[Calendar week]))),Data[Movement type],Data[Movement type])Hope this helps.
Hi romina80
Try using filtered TopN:
Prev Week = VAR _type = 'Table'[Movement type] VAR _week = 'Table'[Calendar week] VAR _prevWeekRow = TOPN ( 1, FILTER ( 'Table', 'Table'[Calendar week] < _week && 'Table'[Movement type] = _type ), [Calendar week], DESC ) RETURN MAXX ( _prevWeekRow, [Actual] )
