Comparing a value across different snapshot weeks in a single table.

[KHSK]

I have a snapshot table and I need to compare whether an Oppty ID in the current week is available in the previous week and derive the Oppty amount if it is unavailable in the previous week. Basically comparing the 2 weeks and deriving what is newly added in the current week. I have 2 slicers to select the weeks-one for Previous and the other for Current.

I need a measure which calculates the above logic and gives me the value as $15000000 from the below table.

Appreciate if someone can help me ASAP. Thanks in advance.

Part 1.

Oppty ID	Oppty Amt	Snapshot Week
1	1000000	8
2	1000000	8
3	1000000	8
4	1000000	8
5	1000000	8
1	1000000	10
2	1000000	10
3	1000000	10
4	1000000	10
5	1000000	10
6	3000000	10
7	3000000	10
8	3000000	10
9	3000000	10
10	3000000	10

[pankajnamekar25 · Accepted solution]

Hello @KHSK 

try this measure

Newly Added Amount =

VAR PrevWeek =

    SELECTEDVALUE('SnapshotTable'[Snapshot Week], 0) -- From Previous Week slicer

VAR CurrWeek =

    SELECTEDVALUE('SnapshotTable (2)'[Snapshot Week], 0) -- From Current Week slicer

VAR PrevIDs =

    CALCULATETABLE (

        VALUES('SnapshotTable'[Oppty ID]),

        'SnapshotTable'[Snapshot Week] = PrevWeek

    )

VAR CurrTable =

    FILTER (

        'SnapshotTable',

        'SnapshotTable'[Snapshot Week] = CurrWeek &&

        NOT 'SnapshotTable'[Oppty ID] IN PrevIDs

    )

RETURN

    SUMX(CurrTable, 'SnapshotTable'[Oppty Amt])

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[johnt75 · Accepted solution]

You'll need 2 tables to use in the slicers. You can create them like

Current Week = DISTINCT( Ops[Snapshot Week] )

Prev Week = DISTINCT( Ops[Snapshot Week] )

Make sure that these tables are not connected to the main table.

Create a measure like

New Amount =
VAR PrevOps = CALCULATETABLE(
    VALUES(Ops[Oppty ID]),
    TREATAS(
        VALUES('Prev Week'[Snapshot Week]),
        Ops[Snapshot Week]
    )
)
VAR CurrentOps = CALCULATETABLE(
    VALUES(Ops[Oppty ID]),
    TREATAS(
        VALUES('Current Week'[Snapshot Week]),
        Ops[Snapshot Week]
    )
)
VAR NewOps = EXCEPT(
    CurrentOps,
    PrevOps
)
VAR Result = CALCULATE(
    SUM(Ops[Oppty Amt]),
    NewOps,
    TREATAS(
        VALUES('Current Week'[Snapshot Week]),
        Ops[Snapshot Week]
    )
)
RETURN
    Result

[ChiragGarg2512 · Accepted solution]

NewOpptyAmount = 
VAR CurrentWeek = 10 # Insert SelectedValue(Current Week Slicer)
VAR PreviousWeek = 8 # Insert SelectedValue(Previous Week Slicer)

VAR CurrentOppties = CALCULATETABLE(
        VALUES(Snapshot[Oppty ID]),
        Snapshot[Snapshot Week] = CurrentWeek
    )

VAR PreviousOppties = CALCULATETABLE(
        VALUES(Snapshot[Oppty ID]),
        Snapshot[Snapshot Week] = PreviousWeek
    )

VAR NewOpptiesOnly = EXCEPT(CurrentOppties, PreviousOppties)

RETURN
CALCULATE(
    SUM(Snapshot[Oppty Amt])
    , Snapshot[Oppty ID] in NewOpptiesOnly
)

Change the column and variable names accordingly.

[KHSK]

Thanks, it worked.

[johnt75 · Accepted solution]

You'll need 2 tables to use in the slicers. You can create them like

Current Week = DISTINCT( Ops[Snapshot Week] )

Prev Week = DISTINCT( Ops[Snapshot Week] )

Make sure that these tables are not connected to the main table.

Create a measure like

New Amount =
VAR PrevOps = CALCULATETABLE(
    VALUES(Ops[Oppty ID]),
    TREATAS(
        VALUES('Prev Week'[Snapshot Week]),
        Ops[Snapshot Week]
    )
)
VAR CurrentOps = CALCULATETABLE(
    VALUES(Ops[Oppty ID]),
    TREATAS(
        VALUES('Current Week'[Snapshot Week]),
        Ops[Snapshot Week]
    )
)
VAR NewOps = EXCEPT(
    CurrentOps,
    PrevOps
)
VAR Result = CALCULATE(
    SUM(Ops[Oppty Amt]),
    NewOps,
    TREATAS(
        VALUES('Current Week'[Snapshot Week]),
        Ops[Snapshot Week]
    )
)
RETURN
    Result

[KHSK]

Thanks, it worked.

[pankajnamekar25 · Accepted solution]

Hello @KHSK 

try this measure

Newly Added Amount =

VAR PrevWeek =

    SELECTEDVALUE('SnapshotTable'[Snapshot Week], 0) -- From Previous Week slicer

VAR CurrWeek =

    SELECTEDVALUE('SnapshotTable (2)'[Snapshot Week], 0) -- From Current Week slicer

VAR PrevIDs =

    CALCULATETABLE (

        VALUES('SnapshotTable'[Oppty ID]),

        'SnapshotTable'[Snapshot Week] = PrevWeek

    )

VAR CurrTable =

    FILTER (

        'SnapshotTable',

        'SnapshotTable'[Snapshot Week] = CurrWeek &&

        NOT 'SnapshotTable'[Oppty ID] IN PrevIDs

    )

RETURN

    SUMX(CurrTable, 'SnapshotTable'[Oppty Amt])

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[KHSK]

Thanks, it worked.