Custom Slicer for Future Time Periods

[kmcferren]

Hi All,

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

Project	Function	Code	Month	Hours	Months Diff
A1	AP	KKAP	6/1/2021	15	1
A1	AP	KKAP	7/1/2021	15	2
A1	AP	KKAP	8/1/2021	15	3
A1	AP	KKAP	9/1/2021	15	4
A1	AP	KLAP	6/1/2021	10	1
A1	AP	KLAP	7/1/2021	10	2
A1	AP	KLAP	8/1/2021	10	3
A1	AP	KLAP	9/1/2021	10	4
A1	AQ	KMAQ	6/1/2021	1	1
A1	AQ	KMAQ	7/1/2021	1	2
A1	AQ	KMAQ	8/1/2021	1	3
A1	AQ	KMAQ	9/1/2021	1	4
A2	AP	KKAP	6/1/2021	40	1
A2	AP	KKAP	7/1/2021	40	2
A2	AP	KLAP	6/1/2021	10	1
A2	AP	KLAP	7/1/2021	10	2
A2	AQ	KMAQ	6/1/2021	1	1
A2	AQ	KMAQ	7/1/2021	1	2

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

[amitchandak · Accepted solution]

@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

[kmcferren]

@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!