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
Anonymous
Not applicable

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

Hi @Anonymous,

 

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

View solution in original post

4 REPLIES 4
BetterCallFrank
Resolver IV
Resolver IV

Hi @Anonymous

 

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 

Anonymous
Not applicable

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

Hi @Anonymous,

 

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

Anonymous
Not applicable

Works Perfectly, thanks @v-ljerr-msft!

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.