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

[KHSK]

I want to calculate Net Positive Change(300,000) & Net Negative change(-400000) for the change in Oppty amt when comparing 2 different snapshot weeks and also the Lost Oppty amt (100,0000)

Appreciate if you could provide me 3 measures to address this issue.

Oppty ID	Oppty Amt	Snapshot Week
1	1000000	8
2	1000000	8
3	1000000	8
4	1000000	8
5	1000000	8
1	1100000	10
2	1200000	10
4	900000	10
5	700000	10

[danextian · Accepted solution]

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

[maruthisp · Accepted solution]

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 

[Ashish_Excel · Accepted solution]

Hi,

Download the PBI file from here.

Hope this helps.

[maruthisp · Accepted solution]

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 

[danextian]

Hi @KHSK 

How did you come up with your expected result? Negative should be -1400,000

Assuming my calculation is correct and that is how really your data is structure (two weeks to compare), try these

Difference = 
VAR _MaxWeek =
    CALCULATE ( MAX ( 'Table'[Snapshot Week] ), ALLSELECTED ( 'Table' ) )
VAR _MinWeek =
    CALCULATE ( MIN ( 'Table'[Snapshot Week] ), ALLSELECTED ( 'Table' ) )
VAR _MaxWeekValue =
    CALCULATE (
        SUM ( 'Table'[Oppty Amt] ),
        KEEPFILTERS( 'Table'[Snapshot Week] = _MaxWeek )
    )
VAR _MinWeekValue =
    CALCULATE (
        SUM ( 'Table'[Oppty Amt] ),
        KEEPFILTERS ('Table'[Snapshot Week] = _MinWeek )
    )
RETURN
    _MaxWeekValue - _MinWeekValue

Net Negative Change =
SUMX (
    FILTER (
        SUMMARIZECOLUMNS ( 'Table'[Oppty ID], "@diff", [Difference] ),
        [@diff] < 0
    ),
    [@diff]
)

Net Positive Change = 
SUMX (
    FILTER (
        SUMMARIZECOLUMNS ( 'Table'[Oppty ID], "@diff", [Difference] ),
        [@diff] > 0
    ),
    [@diff]
)

[KHSK]

Thanks for the quick response Dane. I want to show Oppty ID: 3 in a separate measure as "Lost" and for Net Positive & Net Negative Amts- the Opptys which are existing in both the snapshot weeks with mismatched amts. My bad, I should have mentioned it in my earlier post.

[danextian · Accepted solution]

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