Forum Discussion
how to select the data with a certain range
Hi, Here is the data looks like, and the slicer is the datamonth
I want to make it realize that when the slicer select 202112, the data returns when datamonth>=202101 and datamonth <=slicer 202111,
and when the slicer is 202210, the data returns when datamonth>=202201 and datamonth <=slicer 202210,
do you have any solutions to rezlize that? Thanks.
Hi , Anonymous
Thanks for your destailed explanation and the sample data provided!
Here are the steps you can refer to :
(1)My test data is the same as yours.
(2)We need to click "New Table" as a slicer:
Slicer = VALUES('Table'[datamonth])(3)Then we can create a measure :
Measure = var _slicer = SELECTEDVALUE('Slicer'[datamonth]) var _first =VALUE(LEFT( FORMAT(_slicer , "0"),4)&"01") var _cur_month = MAX('Table'[datamonth]) return IF(_slicer = BLANK() ,1 , IF(_cur_month>= _first && _cur_month <= _slicer ,1,0))(4)Then we can configure the measure on the "Filter on this visual" and we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
- v-yueyunzh-msft
Community Support
Hi , Anonymous
Thanks for your destailed explanation and the sample data provided!
Here are the steps you can refer to :
(1)My test data is the same as yours.
(2)We need to click "New Table" as a slicer:
Slicer = VALUES('Table'[datamonth])(3)Then we can create a measure :
Measure = var _slicer = SELECTEDVALUE('Slicer'[datamonth]) var _first =VALUE(LEFT( FORMAT(_slicer , "0"),4)&"01") var _cur_month = MAX('Table'[datamonth]) return IF(_slicer = BLANK() ,1 , IF(_cur_month>= _first && _cur_month <= _slicer ,1,0))(4)Then we can configure the measure on the "Filter on this visual" and we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I tried measure this way earlier but not work because that I built a realionship between these two tables, I removed the relationship and works, but actually there the realitionship is required because besides this visual, there are other visuals in the same page, and these visuals should be filtered directed by the slicer
- v-yueyunzh-msft
Hi, Anonymous
Yes, in order to implement the logic of your custom slicer, we need that there is no relationship between the two tables, and if the relationship is established, then the data will be filtered, and the best solution to this method is to place two slicers to meet your different needs.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- amitchandak
Super User
Anonymous , You can create a date using year month
date= datevalue([datamonth]&"01")
Create date table and have the month and year there
datamonth = format([Date], "yyyymm")
Create two version of date table, one join with table another do not join
create a measure like
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1*month(_max) ) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
