How to lookup last week data to the same table?

[PBI_newuser]

Hi,

I have a table as below (Table A):
How can I lookup last week difference by using Material in the same table? (Refer to Output Table below)

Table A:

Date	Material	Sales Qty	Repair Qty	Difference (Sales-Repair)
31/12/2020	M-1	56	21	35
7/1/2021	M-7	23	11	12
14/1/2021	M-4	12	3	9
21/1/2021	M-1	99	32	67
28/1/2021	M-2	34	22	12
4/2/2021	M-3	32	15	17

Output Table:

Date	Material	Sales Qty	Repair Qty	Difference (Sales-Repair)	Difference last week
31/12/2020	M-1	56	21	35	-
7/1/2021	M-7	23	11	12	35
14/1/2021	M-4	12	3	9	12
21/1/2021	M-1	99	32	67	9
28/1/2021	M-2	34	22	12	67
4/2/2021	M-3	32	15	17	12

[v-cazheng-msft · Accepted solution]

Hi, @PBI_newuser 

Solution 1 You can create a Calculated column to get the result you want.

SAP-FSL LW =

VAR _preweek =

    CALCULATE (

        MAX ( 'Sample Data'[Date] ),

        FILTER (

            ALL ( 'Sample Data' ),

            'Sample Data'[Material&Location] = EARLIER ( 'Sample Data'[Material&Location] )

                && 'Sample Data'[Date] < EARLIER ( 'Sample Data'[Date] )

        )

    )

VAR _presqty =

    CALCULATE (

        MAX ( 'Sample Data'[SAP Qty] ),

        FILTER (

            ALL ( 'Sample Data' ),

            'Sample Data'[Material&Location] = EARLIER ( 'Sample Data'[Material&Location] )

                && 'Sample Data'[Date] = _preweek

        )

    )

VAR _prefqty =

    CALCULATE (

        MAX ( 'Sample Data'[FSL Qty] ),

        FILTER (

            ALL ( 'Sample Data' ),

            'Sample Data'[Material&Location] = EARLIER ( 'Sample Data'[Material&Location] )

                && 'Sample Data'[Date] = _preweek

        )

    )

VAR result = _presqty - _prefqty

RETURN

    IF ( ISBLANK ( result ), "-", FORMAT ( result, "" ) )

Solution 2 You can also create a Measure.

Measure For SAP-FSL LW =

VAR _curdate =

    MAX ( 'Sample Data'[Date] )

VAR _curmaloc =

    MAX ( 'Sample Data'[Material&Location] )

VAR _preweek =

    CALCULATE (

        MAX ( 'Sample Data'[Date] ),

        FILTER (

            ALL ( 'Sample Data' ),

            'Sample Data'[Material&Location] = _curmaloc

                && 'Sample Data'[Date] < _curdate

        )

    )

VAR _presqty =

    CALCULATE (

        MAX ( 'Sample Data'[SAP Qty] ),

        FILTER (

            ALL ( 'Sample Data' ),

            'Sample Data'[Material&Location] = _curmaloc

                && 'Sample Data'[Date] = _preweek

        )

    )

VAR _prefqty =

    CALCULATE (

        MAX ( 'Sample Data'[FSL Qty] ),

        FILTER (

            ALL ( 'Sample Data' ),

            'Sample Data'[Material&Location] = _curmaloc

                && 'Sample Data'[Date] = _preweek

        )

    )

VAR result = _presqty - _prefqty

RETURN

    IF ( ISBLANK ( result ), "-", result )

The result looks like this:

Here is the sample.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi @v-cazheng-msft Thank you so much for the solution! 🙂

[amitchandak]

@PBI_newuser , for a new column try like

maxx(filter(Table, [Date] = earlier([Date]) -7),[Difference (Sales-Repair)])

For measure 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[PBI_newuser]

Hi @amitchandak 

The column created is incorrect. You may see below the LW (last week) figure is lookup wrongly.

Please help. Below is the sample data for your reference.

https://wetransfer.com/downloads/7f1536d9be35219918f83e28f92af05120210115052823/a3b7ab18c55ba817bce0...