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
krista_003
Frequent Visitor

Subtracting from the same column based on selected filter

Hi Power Bi Community, 

I need your support on writting DAX commend. 

I've a table that consist of project ID and Turnover over the years.  

 

Project IDTurnover MEURYear
ID990,12021
ID980,52021
ID990,32022
ID980,72022
ID990,52023
ID980,92023

 

I'd like to subtract turnover over years, in order to be able calculate the growh. In other words,  
Growth = Turnover 2022 (ID99) - Turnover 2021 (ID99), Turnover 2023 (ID99) - Turnover 2022 (ID99), Turnover 2022 (ID98) - Turnover 2021 (ID98) ...etc. 

Any ideas, how I can write this in DAX command ?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @krista_003 
Please refer to the sample with the solution https://www.dropbox.com/t/80L2KufXOL8k8xLc

1.png2.png

Turnover YOY = 
VAR CurrentYer = SELECTEDVALUE ( Turnover[Year] )
VAR FirstSelectedYear = CALCULATE ( MIN ( Turnover[Year] ), ALLSELECTED ( Turnover ) )
VAR CurrentValue = SUM ( Turnover[Turnover MEUR] )
VAR PreviousValue = CALCULATE ( SUM ( Turnover[Turnover MEUR] ), Turnover[Year] = CurrentYer - 1 )
RETURN
     IF (
         CurrentYer <> FirstSelectedYear,
        CurrentValue - PreviousValue
     )

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @krista_003 
Please refer to the sample with the solution https://www.dropbox.com/t/80L2KufXOL8k8xLc

1.png2.png

Turnover YOY = 
VAR CurrentYer = SELECTEDVALUE ( Turnover[Year] )
VAR FirstSelectedYear = CALCULATE ( MIN ( Turnover[Year] ), ALLSELECTED ( Turnover ) )
VAR CurrentValue = SUM ( Turnover[Turnover MEUR] )
VAR PreviousValue = CALCULATE ( SUM ( Turnover[Turnover MEUR] ), Turnover[Year] = CurrentYer - 1 )
RETURN
     IF (
         CurrentYer <> FirstSelectedYear,
        CurrentValue - PreviousValue
     )

Hi @tamerj1 , 

 

Worked like a charm! Thanks for such detailed sulution! 

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.

Top Solution Authors