DAX Re-using values selected in the slicers

DAX DAX Calculations
1

I have a report that has two slicers, Country and Year range.

From the date year range slicer, i am able to isolate the Minimum and Maximum Years

For example if my Date Years are 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019

Min Year = 2010
Min Selected Year = CALCULATE(MIN('Date'[Year_Number]), ALLSELECTED('Date'[Year_Number]))

Max Year = 2019
Max Selected Year = CALCULATE(MAX('Date'[Year_Number]), ALLSELECTED('Date'[Year_Number]))

This allows me to use these two measure as follows.

Main Title - Economic Outlook by Country = IF(ISFILTERED(DIM_Country[Country]), SELECTEDVALUE(DIM_Country[Country])) & " Economic Outlook Summary from " & [Min Selected Year] & " to " & [Max Selected Year]

Another way i would like to use the measure is to pass them into other measures as follows

Current Account Balance = CALCULATE([Total Current Account Balance], FILTER('Date', 'Date'[Year_Number] = [Max Selected Year]))

However, the Current Account Balance measure is not producing expected results. What is confusing is that i am getting the right amount if i hard-code 2019 as a value in the filter.

Any help is much appreciated!

World Economic Outlook.pbix (1.7 MB)

2

Hi @komengem,

Welcome to the community.

You can use the following measure

Current Account Balance =
VAR _year =
    YEAR (
        MAX ( 'Date'[Date] )
    )
RETURN
    CALCULATE (
        [Total Current Account Balance],
        FILTER (
            'Date',
            'Date'[Year_Number] = _year
        )
    )

Regards,
Harsh Nathani

1 Like
3

Hi @komengem,

Welcome to the Forum!

Think you will find that using variables will solve this.

Current Account Balance =
VAR MaxYear = [Max Selected Year]
RETURN

CALCULATE([Total Current Account Balance], FILTER('Date', 'Date'[Year_Number] = MaxYear))

I hope this is helpful.

1 Like
4

Hi @Melissa, @harsh.nathani

Thank you for the reply. Yes, the introduction of a variable resolved this for me.

5

It’s great to know that you are making progress with your query @komengem. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks