cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors