How to compare last week to this week when there are duplicate IDs

[ThePiecesFit]

Hi everyone,

I've been trying to figure out this issue for about 12 hours total by now. I want to summarize dollars last week vs this week and be able to show the stage last week vs this week, even when there are duplicate IDs. I've tried appending queries, creating reference tables, and creating a variety of DAX formulas and filters, but nothing has worked so far. Please help!

Files: https://drive.google.com/drive/folders/1k2y9V19fEH9JSb6otMlP17l3ZFl1xZHw?usp=sharing 

What I'm aiming for

How it looks

Last Week

ID	Manager	Rep Name	Stage	Dollars
A	Tom	Bob	1	$  226,000.00
A	Fernanda	Kevin	1	$  155,000.00
A	Fernanda	Stacy	1	$  155,000.00
B	Tom	Lacy	3	$    78,000.00
B	Fernanda	Paul	3	$    55,000.00

This Week

ID	Manager	Rep Name	Stage	Dollars
A	Tom	Bob	2	$  142,000.00
A	Fernanda	Kevin	2	$  188,000.00
B	Tom	Lacy	4	$    76,000.00
B	Tom	Jack	4	$    10,000.00
B	Fernanda	Paul	4	$    66,000.00

[amitchandak]

@ThePiecesFit , If these are two tables you need to have common dimensions ID, Manager, Rep Name, Stage. These may be part of one or more dimensions. And Join these two table with these and then analyse together with common dimensions and this week and last week as measures

[ThePiecesFit]

Hi @amitchandak, do you recommend I create a unique ID in power query by concatenating those dimensions and use that to join the tables? Or what do you mean exactly? 

[amitchandak · Accepted solution]

@ThePiecesFit , A common dimension with concatenated keys, Or more than one dimensions