Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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?
Yes there might be some times where I select more then 2 years
Sorry actually just stick with only selecting two years
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?
Year | Destination Name | Tonnes |
2009 | Vietnam | 271400 |
2010 | Vietnam | 24052 |
2011 | Vietnam | 119859 |
2016 | Vietnam | 312212 |
2017 | Vietnam | 432490 |
2018 | Vietnam | 133378 |
2016 | United States | 110647 |
2016 | United Arab Emirates | 113401 |
2017 | United Arab Emirates | 97545 |
2009 | Turkey | 141502 |
2011 | Turkey | 74456 |
2016 | Turkey | 43998 |
2009 | Thailand | 1601450 |
2010 | Thailand | 1476372 |
2011 | Thailand | 1623935 |
2012 | Thailand | 1150971 |
2013 | Thailand | 1443006 |
2014 | Thailand | 841360 |
2015 | Thailand | 2358233 |
2016 | Thailand | 2136401 |
2017 | Thailand | 158288 |
2018 | Thailand | 140888 |
2009 | Tanzania | 73939 |
2007 | Taiwan | 10916425 |
2008 | Taiwan | 12337200 |
2009 | Taiwan | 9436000 |
2010 | Taiwan | 11616279 |
2011 | Taiwan | 11555438 |
2012 | Taiwan | 8225387 |
2013 | Taiwan | 10436359 |
2014 | Taiwan | 11415221 |
2015 | Taiwan | 12091974 |
2016 | Taiwan | 14366376 |
2017 | Taiwan | 13337811 |
2018 | Taiwan | 4560391 |
2009 | Spain | 799636 |
2010 | Spain | 58538 |
2015 | Spain | 246798 |
2007 | South Korea | 9007476 |
2008 | South Korea | 14170017 |
2009 | South Korea | 16080704 |
2010 | South Korea | 15583231 |
2011 | South Korea | 17826925 |
2012 | South Korea | 14501953 |
2013 | South Korea | 12979839 |
2014 | South Korea | 15018934 |
2015 | South Korea | 17350646 |
2016 | South Korea | 12845298 |
2017 | South Korea | 13778076 |
2018 | South Korea | 3464130 |
2017 | South Africa | 82496 |
2008 | Singapore | 601617 |
2009 | Singapore | 161278 |
2010 | Singapore | 213198 |
2011 | Singapore | 52515 |
2012 | Singapore | 180492 |
2013 | Singapore | 454518 |
2016 | Singapore | 58009 |
2018 | Singapore | 77000 |
2007 | Philippines | 66683 |
2008 | Philippines | 137705 |
2010 | Philippines | 68205 |
2011 | Philippines | 552069 |
2012 | Philippines | 343389 |
2013 | Philippines | 426091 |
2014 | Philippines | 134747 |
2015 | Philippines | 476026 |
2016 | Philippines | 1080855 |
2017 | Philippines | 1315225 |
2018 | Philippines | 457363 |
2008 | Pakistan | 91429 |
2009 | Pakistan | 52701 |
2010 | Pakistan | 110137 |
2011 | Pakistan | 55000 |
2012 | Pakistan | 55000 |
2014 | Pakistan | 54977 |
2007 | New Caledonia | 137996 |
2008 | New Caledonia | 264600 |
2009 | New Caledonia | 138934 |
2010 | New Caledonia | 285658 |
2011 | New Caledonia | 200927 |
2012 | New Caledonia | 238159 |
2013 | New Caledonia | 197396 |
2014 | New Caledonia | 353323 |
2015 | New Caledonia | 588109 |
2016 | New Caledonia | 938709 |
2017 | New Caledonia | 1045784 |
2018 | New Caledonia | 457635 |
2009 | Netherlands | 463439 |
2014 | Netherlands | 135298 |
2016 | Netherlands | 693209 |
2017 | Netherlands | 152871 |
2007 | Mexico | 3312570 |
2008 | Mexico | 194886 |
2009 | Mexico | 2523484 |
2010 | Mexico | 1839625 |
2011 | Mexico | 2847369 |
2012 | Mexico | 3174503 |
2013 | Mexico | 662301 |
2014 | Mexico | 1923172 |
2015 | Mexico | 2568862 |
2016 | Mexico | 1809767 |
2017 | Mexico | 141644 |
2007 | Malaysia | 1680081 |
2008 | Malaysia | 1934353 |
2009 | Malaysia | 557141 |
2010 | Malaysia | 752979 |
Hi,
Try this measure
=CALCULATE(SUM(Data[Tonnes]),FILTER(Data,MAX(Data[Year])))-CALCULATE(SUM(Data[Tonnes]),FILTER(Data,MIN(Data[Year])))
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |