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

Subtracting from the same column based on select filters

Hello,

I was wondering if someone can help me with a DAX formula.  I currently have data similar to this:

 

I have a year range from 2007 - 2018. I would like to create a DAX formula that would calculate the difference of the last column when I select 2 years based on the years selected in a filter. E.g. if i have selected 2009 and 2011 Vietnam would be  119,859 - 271,400 = -151,541 or if it was vietnam 2009 and 2017 432,490 - 271,400 = 161,090

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

Will you only be selecting 2 years always in the slicer.  Can there ever be a case where you will select more than 2 years in the slicer?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes there might be some times where I select more then 2 years

HI @bucket8

 

What do you want to happen when you have selected 3 or more years from your slicer?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Sorry actually just stick with only selecting two years

Phil_Seamark
Employee
Employee

Hi @bucket8

 

I can help with that.  Do you have some sample data that I can paste into a model to build the calc with?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

YearDestination NameTonnes
2009Vietnam271400
2010Vietnam24052
2011Vietnam119859
2016Vietnam312212
2017Vietnam432490
2018Vietnam133378
2016United States110647
2016United Arab Emirates113401
2017United Arab Emirates97545
2009Turkey141502
2011Turkey74456
2016Turkey43998
2009Thailand1601450
2010Thailand1476372
2011Thailand1623935
2012Thailand1150971
2013Thailand1443006
2014Thailand841360
2015Thailand2358233
2016Thailand2136401
2017Thailand158288
2018Thailand140888
2009Tanzania73939
2007Taiwan10916425
2008Taiwan12337200
2009Taiwan9436000
2010Taiwan11616279
2011Taiwan11555438
2012Taiwan8225387
2013Taiwan10436359
2014Taiwan11415221
2015Taiwan12091974
2016Taiwan14366376
2017Taiwan13337811
2018Taiwan4560391
2009Spain799636
2010Spain58538
2015Spain246798
2007South Korea9007476
2008South Korea14170017
2009South Korea16080704
2010South Korea15583231
2011South Korea17826925
2012South Korea14501953
2013South Korea12979839
2014South Korea15018934
2015South Korea17350646
2016South Korea12845298
2017South Korea13778076
2018South Korea3464130
2017South Africa82496
2008Singapore601617
2009Singapore161278
2010Singapore213198
2011Singapore52515
2012Singapore180492
2013Singapore454518
2016Singapore58009
2018Singapore77000
2007Philippines66683
2008Philippines137705
2010Philippines68205
2011Philippines552069
2012Philippines343389
2013Philippines426091
2014Philippines134747
2015Philippines476026
2016Philippines1080855
2017Philippines1315225
2018Philippines457363
2008Pakistan91429
2009Pakistan52701
2010Pakistan110137
2011Pakistan55000
2012Pakistan55000
2014Pakistan54977
2007New Caledonia137996
2008New Caledonia264600
2009New Caledonia138934
2010New Caledonia285658
2011New Caledonia200927
2012New Caledonia238159
2013New Caledonia197396
2014New Caledonia353323
2015New Caledonia588109
2016New Caledonia938709
2017New Caledonia1045784
2018New Caledonia457635
2009Netherlands463439
2014Netherlands135298
2016Netherlands693209
2017Netherlands152871
2007Mexico3312570
2008Mexico194886
2009Mexico2523484
2010Mexico1839625
2011Mexico2847369
2012Mexico3174503
2013Mexico662301
2014Mexico1923172
2015Mexico2568862
2016Mexico1809767
2017Mexico141644
2007Malaysia1680081
2008Malaysia1934353
2009Malaysia557141
2010Malaysia752979

Hi,

 

Try this measure

 

=CALCULATE(SUM(Data[Tonnes]),FILTER(Data,MAX(Data[Year])))-CALCULATE(SUM(Data[Tonnes]),FILTER(Data,MIN(Data[Year])))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.