Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ThePiecesFit
Advocate II
Advocate II

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

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

 

Screenshot 2021-02-14 195058.png

 

How it looks

 

 

Screenshot 2021-02-14 200655.png

 

 

Last Week

 

IDManagerRep NameStage Dollars 
ATomBob 1 $  226,000.00
AFernandaKevin1 $  155,000.00
AFernandaStacy1 $  155,000.00
BTomLacy3 $    78,000.00
BFernandaPaul3 $    55,000.00

 

This Week

 

IDManagerRep NameStage Dollars 
ATomBob 2 $  142,000.00
AFernandaKevin2 $  188,000.00
BTomLacy4 $    76,000.00
BTomJack4 $    10,000.00
BFernandaPaul4 $    66,000.00

 

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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

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? 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.