Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
xl0911
Helper III
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
rbriga
Impactful Individual
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!

View solution in original post

5 REPLIES 5
xl0911
Helper III
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 ?

rbriga
Impactful Individual
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!

Perfect ! thank you very mutch !

rbriga
Impactful Individual
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!
rbriga
Impactful Individual
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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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