cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jak82 Member
Member

Find out Value of filter and use in Measure

Hi,

 

I have a measure which uses

 

=-CALCULATE (
        SUM ( Budget[Budget Data] ),
        FILTER ( ALL ( 'Sagecalcodes' ), INT('Sagecalcodes'[Year]) = YEAR(TODAY())
    ))

This only gives me 2017, so if someone goes back to 2016 it still shows 2017, how would I get what date is on the filter into DAX.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Find out Value of filter and use in Measure for Profit and Loss

Hi @jak82,

 

Could you try the formula below to see if it works in your scenario?Smiley Happy

=
VAR currentSelectedYear =
    MAX ( 'Sagecalcodes'[Year] )
RETURN
    - CALCULATE (
        SUM ( Budget[Budget Data] ),
        FILTER (
            ALL ( 'Sagecalcodes' ),
            INT ( 'Sagecalcodes'[Year] ) = currentSelectedYear
        )
    )

 

Regards

4 REPLIES 4
Highlighted

Re: Find out Value of filter and use in Measure

Hi @jak82

 

the beauty is: you dont have to, it's all been taken care of as soon as you add a second visual to the page (a slicer) and put the 

'Sagecalcodes'[Year]

Column on it,

 

remove the entire filter expression from your measure code, you dont need it :-)

 

HTH,

Frank 

jak82 Member
Member

Re: Find out Value of filter and use in Measure for Profit and Loss

Thanks Frank, sorry should have explained it better. I am trying to create a Profit and Loss matrix

 

I have two slicers on the screen one is 

'Sagecalcodes'[Year]

the other is

'Sagecalcodes'[Months]

 

I am wanting to have it in the same table.  Due one of slicers being Months I need to add my own filter measure, any idea how to do that?

 

TIA

v-ljerr-msft Super Contributor
Super Contributor

Re: Find out Value of filter and use in Measure for Profit and Loss

Hi @jak82,

 

Could you try the formula below to see if it works in your scenario?Smiley Happy

=
VAR currentSelectedYear =
    MAX ( 'Sagecalcodes'[Year] )
RETURN
    - CALCULATE (
        SUM ( Budget[Budget Data] ),
        FILTER (
            ALL ( 'Sagecalcodes' ),
            INT ( 'Sagecalcodes'[Year] ) = currentSelectedYear
        )
    )

 

Regards

jak82 Member
Member

Re: Find out Value of filter and use in Measure for Profit and Loss

Works Perfectly, thanks @v-ljerr-msft!