Forum Discussion
Filter report on date between other column dates
Hi, community,
I have a table, such as:
| Project Id | Cycle Id | Cycle Start | Cycle Finish |
| 1 | 1 | 1/02/2021 | 5/03/2021 |
| 2 | 1 | 1/02/2021 | 5/03/2021 |
| 3 | 2 | 8/03/2021 | 11/03/2021 |
| 4 | 2 | 8/03/2021 | 11/03/2021 |
| 5 | 2 | 8/03/2021 | 11/03/2021 |
In the report, I want to filter out all projects which are not in the current cycle.
Thanks
Evan
amitchandak , I have figured out how to do this.
To explain a little more about the task, I have different reports on current project cycle, next project cycle and so on. Thus, I needed to filter out the content by cycle timeframe.
I used Greg Deckler's reply here https://community.powerbi.com/t5/Desktop/Power-Query-If-x-number-is-between-x-and-y-then-quot-Delete-quot/m-p/96645/highlight/true#M40712 to index all current and future cycles, so that I got 1 for current, 2 for next etc., and applied page filter based on the cycle number.
Thanks
Evan
3 Replies
- amitchandak
Super User
Anonymous , Can you explain with examples. The expected output is not clear to me .
Try a measure like this with independent date table
measure =
var _max1 = maxx(allselected('Date'), Date[Date])
var _min1 = minx(allselected('Date'), Date[Date])
return
calculate(count([Project Id]), filter('Table', 'Table'[Cycle Finish] <=_max1 && 'Table'[Cycle Finish] >=_min1 ))amitchandak , I have figured out how to do this.
To explain a little more about the task, I have different reports on current project cycle, next project cycle and so on. Thus, I needed to filter out the content by cycle timeframe.
I used Greg Deckler's reply here https://community.powerbi.com/t5/Desktop/Power-Query-If-x-number-is-between-x-and-y-then-quot-Delete-quot/m-p/96645/highlight/true#M40712 to index all current and future cycles, so that I got 1 for current, 2 for next etc., and applied page filter based on the cycle number.
Thanks
Evan
- amitchandak
Anonymous , Thanks for the update. Kudos to you. We all are learning from Greg's blogs and solutions.
