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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MainlineData
Frequent Visitor

Utilising another column when doing calculation (from same table)

Hey,

 

I've been trying to do this for ages now, and I either have missed something very simple or what I'm trying to do isn't plausible.

 

I'll try include as much detail as possible.

 

At present, I have a product table:

Product IDOriginal SeasonPrevious Season
0AW21AW20
1AW22AW21
2ContCont
3SS22SS21
4SS23SS22


My product table is joined to my raw data table by the product ID.

At present when trying to show this data vs. last year, I get the following (as expected):

Original SeasonTYLY
AW21£5,000£200,000
AW22£100,000£0
Cont£50,000£25,000
SS22£5,000£125,000
SS23£120,000£0

 

Now, my desired outcome, and the thing I haven't cracked:

Original SeasonTYLY
AW21£5,000-
AW22£100,000£200,000 (value of AW21 LY)
Cont£50,000£25,000
SS22£5,000-
SS23£120,000

£125,000 (value of SS22 LY)

 

As you can see, for TY I'm wanting to use the 'Original Season', but when doing the LY calculation I want it to look at the 'Previous Season' instead, which as seen from Product Key is in the same table.

I've tried combinations of TREATAS/VALUES, and disconnected tables but I can't quite it perfect.

 

In another file, I pull the LY separately and then just overwrite to the value I want it to sit on (e.g. I would overwrite AW21 in the LY data to AW22, so when looking at AW22 TY and LY it appears in both sides), but I'm trying to do this in my main dataset which is product level.

 

 

1 REPLY 1
tamerj1
Super User
Super User

Hi @MainlineData 
What DAX have you tried?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors