Unfilter measure with slicer date only the less side

[xl0911]

Hello,

I have a Year slicer (2017, 2018, ....) and a Month slicer (Jan, Feb,....) and in this dashboard all the charts that connect to those slicers are working very good.

My issue is that I want to add a measure (and a chart) that will connect to those slicers but on the "less" side and not on the "Greater" side, and what I mean by that, for example if I select 2020 in the Year slicer and Apr in the Month slicer this measure needs to filter by:  <= April 2020     and not  <= April 2020 <=   (as it by defaulte).

The ather measures needs to stay the same.

The measure is this formula:

MyMeasyre = CALCULATE( SUM(Sales[Price]) , USERELATIONSHIP(Tbl_Dates[Date], Sales[SaleDate])

[xl0911]

Thank you, very nice solution, but if all slicer is open (nothing is selected) the it gets the MIN value which it makes more sense if nothing selected I rather gets the MAX... is it possible ?

[rbriga · Accepted solution]

Try changing the first two vars:

VAR _Year = MAX('Tbl_Dates'[Year])   

VAR _Year = MAX('Tbl_Dates'[Month NUMBER])

Do you get the desired outcome now?

[xl0911]

Perfect ! thank you very mutch !

[rbriga]

Happy to help

[rbriga]

First, it seems you have a dates table. I'm not sure why you need to use USERELATIONSHIP(Tbl_Dates[Date], Sales[SaleDate]) if the relationship is already established, though.

Now, You'll need a measure to calculate the sum until the selected date.

I assume that the year and month filters relate to the Tbl_Dates table.

I'll suggest:

Sales Up to Date=

VAR _Year = MIN('Tbl_Dates'[Year])   

VAR _Year = MIN('Tbl_Dates'[Month NUMBER])  --MIN would cause this VAR to be 1 (January) if no moth is selected

VAR _LimitDate = DATE(_Year,_Month,1)

RETURN

CALCULATE(

SUM(SUM(Sales[Price]),

REMOVEFILTERS('Tbl_Dates'),    --Since we don't really want to filter to these dates

KEEPFILTERS(Sales[SaleDate]<_LimitDate)

)

Give it a try.