Comparing 2 snapshot weeks in a single table for Net positive and negative change in amounts

Try the following measures:

Min Week Value = 
VAR _MinWeek =
    CALCULATE ( MIN ( 'Table'[Snapshot Week] ), ALLSELECTED ( 'Table' ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Oppty Amt] ),
        KEEPFILTERS ( 'Table'[Snapshot Week] = _MinWeek )
    )


Max Week Value = 
VAR _MaxWeek =
    CALCULATE ( MAX ( 'Table'[Snapshot Week] ), ALLSELECTED ( 'Table' ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Oppty Amt] ),
        KEEPFILTERS ( 'Table'[Snapshot Week] = _MaxWeek )
    )


Difference = 
[Max Week Value] - [Min Week Value]


Net Positive Change = 
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Table'[Oppty ID],
            "@diff", [Difference],
            "@max", [Max Week Value]
        ),
        [@diff] > 0
            && NOT ( ISBLANK ( [@max] ) )
    ),
    [@diff]
)


Net Negative Change = 
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Table'[Oppty ID],
            "@diff", [Difference],
            "@max", [Max Week Value]
        ),
        [@diff] < 0
            && NOT ( ISBLANK ( [@max] ) )
    ),
    [@diff]
)


Lost = 
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Table'[Oppty ID],
            "@value", CALCULATE ( SUM ( 'Table'[Oppty Amt] ) ),
            "@max", [Max Week Value]
        ),
        ISBLANK ( [@max] )
    ),
    [@value]
)

Hi KHSK ,

Please try the below steps as per my understanding and with based on the sample data.
Assume, you have 3 columns in the data set as - Oppty ID, Oppty Amt and Snapshot Week.
Comparing 2 snapshot weeks.pbix

Net Positive Change =
VAR CurrentWeek = 10
VAR PreviousWeek = 8

VAR PrevSnapshot =
FILTER(Opportunities, Opportunities[Snapshot Week] = PreviousWeek)

VAR CurrSnapshot =
FILTER(Opportunities, Opportunities[Snapshot Week] = CurrentWeek)

VAR Joined =
NATURALINNERJOIN (
SELECTCOLUMNS(PrevSnapshot, "Oppty ID", Opportunities[Oppty ID], "PrevAmt", Opportunities[Oppty Amt]),
SELECTCOLUMNS(CurrSnapshot, "Oppty ID", Opportunities[Oppty ID], "CurrAmt", Opportunities[Oppty Amt])
)

RETURN
SUMX(
FILTER(Joined, [CurrAmt] > [PrevAmt]),
[CurrAmt] - [PrevAmt]
)

Net Negative Change =
VAR CurrentWeek = 10
VAR PreviousWeek = 8

VAR PrevSnapshot =
FILTER(Opportunities, Opportunities[Snapshot Week] = PreviousWeek)

VAR CurrSnapshot =
FILTER(Opportunities, Opportunities[Snapshot Week] = CurrentWeek)

VAR Joined =
NATURALINNERJOIN (
SELECTCOLUMNS(PrevSnapshot, "Oppty ID", Opportunities[Oppty ID], "PrevAmt", Opportunities[Oppty Amt]),
SELECTCOLUMNS(CurrSnapshot, "Oppty ID", Opportunities[Oppty ID], "CurrAmt", Opportunities[Oppty Amt])
)

RETURN
SUMX(
FILTER(Joined, [CurrAmt] < [PrevAmt]),
[CurrAmt] - [PrevAmt]
)

Lost Opportunity Amount =
VAR CurrentWeek = 10
VAR PreviousWeek = 8

VAR PrevSnapshot =
FILTER(Opportunities, Opportunities[Snapshot Week] = PreviousWeek)

VAR CurrIDs =
SELECTCOLUMNS(
FILTER(Opportunities, Opportunities[Snapshot Week] = CurrentWeek),
"Oppty ID", Opportunities[Oppty ID]
)

RETURN
SUMX(
FILTER(PrevSnapshot, NOT Opportunities[Oppty ID] IN CurrIDs),
Opportunities[Oppty Amt]
)

Note - You can replace 8 and 10 with slicer-bound variables or parameters if you want to make it interactive.

Please let me know if you have further questions. Thanks in advance!

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated — thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X 

Hi,

Download the PBI file from here.

Hope this helps.