Calculate previous week values

[romina80]

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

[danextian]

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] )

[Ashish_Mathur]

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.

[ajaybabuinturi · Accepted solution]

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.

[MM_DATA]

have you got a date tabel ?