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
kmcferren
Frequent Visitor

Custom Slicer for Future Time Periods

Hi All,

 

New to Power BI and struggling with creating slicer to filter on custom time periods into the future.   Sample data is below.

 

ProjectFunctionCodeMonthHoursMonths Diff
A1APKKAP6/1/2021151
A1APKKAP7/1/2021152
A1APKKAP8/1/2021153
A1APKKAP9/1/2021154
A1APKLAP6/1/2021101
A1APKLAP7/1/2021102
A1APKLAP8/1/2021103
A1APKLAP9/1/2021104
A1AQKMAQ6/1/202111
A1AQKMAQ7/1/202112
A1AQKMAQ8/1/202113
A1AQKMAQ9/1/202114
A2APKKAP6/1/2021401
A2APKKAP7/1/2021402
A2APKLAP6/1/2021101
A2APKLAP7/1/2021102
A2AQKMAQ6/1/202111
A2AQKMAQ7/1/202112

 

I created Time Period Table with slicer options:

Time Period Slicer
Next 2 Months
All Months

 

Then created Time Period Filter and applied to my visual, setting equal to "Y":

Time Period Filter = IF(
AND(SELECTEDVALUE('Time Period'[Time Period Slicer])="Next 2 Months",SELECTEDVALUE(Data[Months Diff])<=2),"Y",
IF(SELECTEDVALUE('Time Period'[Time Period Slicer])="All Months","Y","N"))
 
When filtering on "Next 2 Months", total hours does not filter correctly unless I have either Month or Months Diff in the view.  I need to be able to show totals at higher level without including Month and/or Months Diff.  I believe SELECTEDVALUE is the problem because it can't determine one value across multiple months, however I cannot work out what it should be... I'm thinking I need to use CALCULATE but I can't figure out the aggregation to make it work to display by project or function or code only, across time period selected, can someone please help?
 
Many thanks!
Kelly
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@kmcferren , I think you should try measure like this example

//used date table

 

Measure =
var _max = maxx(allselected('Date'), Date[Date])
var _min = eomonth(_max,-3)+1
var _sel = 'Time Period'[Time Period Slicer])
return
Switch(True() ,
_sel = ="Next 2 Months",calculate(Sum(Table[total hours]), filter('Date', 'Date'[Date] >= Min && ' Date'[Date] <= _max)),
_sel = ="All Months",calculate(Sum(Table[total hours]))
)

 

or

Measure =
var _max = maxx(allselected('Date'), Date[Date])
var _min = eomonth(_max,-3)+1
var _sel = 'Time Period'[Time Period Slicer])
return
Switch(True() ,
_sel = ="Next 2 Months",calculate(Sum(Table[total hours]), filter(all('Date'), 'Date'[Date] >= Min && ' Date'[Date] <= _max)),
_sel = ="All Months",calculate(Sum(Table[total hours]),all('Date'))
)

 

if needed refer

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@kmcferren , I think you should try measure like this example

//used date table

 

Measure =
var _max = maxx(allselected('Date'), Date[Date])
var _min = eomonth(_max,-3)+1
var _sel = 'Time Period'[Time Period Slicer])
return
Switch(True() ,
_sel = ="Next 2 Months",calculate(Sum(Table[total hours]), filter('Date', 'Date'[Date] >= Min && ' Date'[Date] <= _max)),
_sel = ="All Months",calculate(Sum(Table[total hours]))
)

 

or

Measure =
var _max = maxx(allselected('Date'), Date[Date])
var _min = eomonth(_max,-3)+1
var _sel = 'Time Period'[Time Period Slicer])
return
Switch(True() ,
_sel = ="Next 2 Months",calculate(Sum(Table[total hours]), filter(all('Date'), 'Date'[Date] >= Min && ' Date'[Date] <= _max)),
_sel = ="All Months",calculate(Sum(Table[total hours]),all('Date'))
)

 

if needed refer

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

@amitchandak 

 

Thank you so much!  I was able to use the syntax of FILTER within CALCULATE which you suggested to get this to work:

 

Hours =

IF('Time Period'[Time Period]="Next 2 Months",

calculate(Sum('Data'[Hours]), filter('Data', 'Data'[Months Diff] >= 1 && 'Data'[Months Diff] <= 2)),

calculate(Sum('Data'[Hours])))

 

Thanks again!

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.