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
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":
Solved! Go to 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 , 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
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |