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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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