Forum Discussion
Post PartisanFilter projects between the Start and End Date
Hello all,
I have a start and end date for a project. And i am visualizing this on Road map custom app.
I need to create a slicer/parameter. If user inputs/selects 2019(Start date),2022(End date) then i need to filter projects which starts and end between 2019&2022 and also projects which are passing this year.
| Project Number | Start Date | End Date |
| PRJ-08-00000084 | 08-05-2018 | 17-02-2022 |
| PRJ-09-00000026 | 08-01-2019 | 13-05-2021 |
| PRJ-08-00000096 | 06-05-2021 | 09-03-2023 |
| PRJ-21-00090376 | 20-05-2021 | 22-12-2022 |
| PRJ-21-00090413 | 20-05-2021 | 30-11-2023 |
| PRJ-08-00000095 | 10-05-2022 | 11-05-2023 |
amitchandak please help.
harshagraj if user are entering the year , and not selecting from a date table, change the measure:
Started in range = //you don't need minx and maxx, these are iterators var _max = DATE ( [ParameterStartYearValue], 1, 1 ) var _min =DATE ( [ParameterEndYearValue], 12, 31 ) return calculate(count(Table[Project Number]), //no need to use filter iterator //Filter(Table, Table[StartDate] <=_max, Table[StartDate]) >=_min )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
harshagraj I guess this is what you need:
Started in range = var _min = DATE ( [StartYearValue], 1, 1 ) var _max = DATE ( [EndYearValue], 12, 31 ) return calculate ( count ( Projects[Project Number] ), ( Projects[Start Date] >= _min && Projects[Start Date] <=_max ) || ( Projects[End Date] >=_min && Projects[End Date] <=_max ) )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
6 Replies
- parry2k
Super User
harshagraj if user are entering the year , and not selecting from a date table, change the measure:
Started in range = //you don't need minx and maxx, these are iterators var _max = DATE ( [ParameterStartYearValue], 1, 1 ) var _min =DATE ( [ParameterEndYearValue], 12, 31 ) return calculate(count(Table[Project Number]), //no need to use filter iterator //Filter(Table, Table[StartDate] <=_max, Table[StartDate]) >=_min )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
harshagraj I guess this is what you need:
Started in range = var _min = DATE ( [StartYearValue], 1, 1 ) var _max = DATE ( [EndYearValue], 12, 31 ) return calculate ( count ( Projects[Project Number] ), ( Projects[Start Date] >= _min && Projects[Start Date] <=_max ) || ( Projects[End Date] >=_min && Projects[End Date] <=_max ) )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- amitchandak
harshagraj , If you have date table joined with Start date that should do. Only count(Table[Project Numer]) is needed
But
With help from and independent date table
Started in range =
var _max = minx(allselected(Date), Date[Date])
var _min = maxx(allselected(Date), Date[Date])
return
calculate(count(Table[Project Number]), Filter(Table,(Table[StartDate] <=_max && Table[StartDate]) >=_min) )
Started in range and end after Range =
var _max = minx(allselected(Date), Date[Date])
var _min = maxx(allselected(Date), Date[Date])
return
calculate(count(Table[Project Number]), Filter(Table,(Table[StartDate] <=_max && Table[StartDate]) >=_min && Table[EndDate] >_max) )Also check how you work with both start and end date. You can avoid any active join
- parry2k
amitchandak few improvement on your DAX:
Started in range = //you don't need minx and maxx, these are iterators var _max = MIN ( Date[Date] ) // minx(allselected(Date), Date[Date]) var _min = MAX ( Date[Date] ) //maxx(allselected(Date), Date[Date]) return calculate(count(Table[Project Number]), //no need to use filter iterator //Filter(Table, Table[StartDate] <=_max, Table[StartDate]) >=_min )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
harshagrajHello parry2k amitchandak thanks for the quick answers but i am confused.
I have created two paramaters Start Date & End Date and user will input the required year. But from the calculation you provided i am not able to understand.- harshagraj
Hello parry2k please see the image. If Parameter start and end date is 2019 and 2021 i need Projects starting from 2019 and ends in 2021 and also which are passing in between these dates.
Eg: PRJ-08-00000084 Year(Start date) is 2018 but it is passing in 2019 also becaue the year(end date) is 2022.
