cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Unfilter measure with slicer date only the less side

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])

1 ACCEPTED SOLUTION
Impactful Individual

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?

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
5 REPLIES 5
Helper III

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 ?

Impactful Individual

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?

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
Helper III

Perfect ! thank you very mutch !

Impactful Individual

Happy to help

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
Impactful Individual

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.

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors