The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Solved! Go to 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.
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
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.
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.
Thanks, it worked.
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
Thanks, it worked.
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.
Thanks, it worked.