Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |