Comparation of opp-ty with it's snapshot

[bsas]

Hi PBI Experts,

I'll have a problem with comparation of data and do not know if such thing is possible  in PBI.

I have 2 sql tables: "opp_ty" and "opp-ty snaphot". Each record in table 1 has unic ID and some other columns. Table 2 has weekly snapshots of opp-ties from table1 (it has the same columns as table1 + date of snapshot). 1 to N.

How I can compare original data in record of opp-ty from table1 with it's snapshot data: 1 week before or 2 weeks before of 3 weeks before...etc (week before means snaphot for last week) and show if something change.

[erik_tarnvik]

Hi @bsas,

obviously the answer depends on more specifically what your data looks like and how you want to show the changes, but just to get this started, assume your tables look like this:

opp_ty:
ID	Week	Value
A	4	100
B	4	200
C	4	300

opp_ty snap:
ID	Week	Value
A	1	90
A	2	100
A	3	120
B	1	200
B	2	200
B	3	200
C	1	250
C	2	280
C	3	300

You could then define a measure:

Measure = IF(MAX('opp_ty snap'[Value]) <> MAX(opp_ty[Value]), 
             MAX('opp_ty snap'[Value]) - MAX(opp_ty[Value]), 
             BLANK())

Then create a matrix visual with opp_ty[ID] on the rows, 'opp_ty snap'[Week] on the columns and [Measure] as values. For my sample data set you would then get:

Meaning, compared to the value in your base table for week 4, B hasn't changed at all and doesn't appear in the table. A had a Value that was 20 higher in week 3, had the same value as now in week 2 and was 10 lower in week 1. And so on. Only your imagination is a limit as to how you would show this though, this is just the simplest representation I could think of.

[bsas]

Thanks @erik_tarnvik,

And if I need show comparation (I use table with column "changes", where I show all changes for one opp-ty in one row like: date changed;amount changed;lost) for specific week (value in slicer). Changes probably will be measure.

[erik_tarnvik]

Hi @bsas,

can you please provide an example of what you would like the end result to look like?  You mention a slicer on week but at the same time, the snapshots are weekly so are you saying you just want the change as compared to the week before? In addition, displaying changes without also displaying the value that the change is relative to seems less than useful. An example would be extremely useful and save time.

Also, what is the definition of "lost"? You already have "changed", what is the difference?

In short, I need more specifics in order to be helpful.

[bsas]

@erik_tarnvik please find below exmples

Ihave one table with current results:

ID	name	close date	amount	status	type
1	opp-1	10/26/2017	$     10,000.00	open	forecasted
2	opp-2	10/30/2017	$     50,000.00	won	won
3	opp-3	10/30/2017	$     30,000.00	lost	lost
4	opp-4	10/26/2017	$       7,000.00	open	best case
5	opp-5	10/30/2017	$   440,000.00	open	forecasted

One table with snapshots (they are on weekly basis, one snapshot includes all changes per week)

ID	name	close date	amount	status	type	snapshot date
1	opp-1	10/30/2017	$     50,000.00	open	best case	10/23/2017
1	opp-1	10/26/2017	$     45,000.00	open	best case	10/16/2017
1	opp-1	10/26/2017	$     30,000.00	open	best case	10/9/2017
2	opp-2	10/10/2017	$     65,000.00	open	forecasted	10/23/2017
2	opp-2	10/20/2017	$     65,000.00	open	best case	10/23/2017
3	opp-3	10/30/2017	$     30,000.00	lost	best case	10/23/2017

what I want to achive when using slicer:

week before comparation

name	changes
opp-1	close date changed to 10/26/2017; amount decreased (-40K);Status changed to Forecasted
opp-2	close dae changed to 10/30/2017;amount increased (+15K);won

2 weeks before comparation:

name	changes
opp-1	amount decreased (-35K);status changed to forecasted
opp-2	close dae changed to 10/30/2017;amount increased (+15K);won