Forum Discussion
Filtering the data
I have a 'Projects' table in my report with a 'project start date' column. I have two tables 'Period' and 'Month+year' they are connected to the 'project start date'. In the report, I would like to have the option to filter by date in two ways:
- By period: current (from today until the end of the year), future (next year), and past.
- By month + year.
Currently, it looks like both of these filters exclude each other which I understand and it's correct behaviour but I would like to be able to filter, for example, the current period and additionally two months from the next year (which belong to the future period).Is this even possible?
Thanks for dharmendars007 's reply ,please allow me to provide another insight.
Hi domi25 ,
Please try the following steps.
Create two slicer table.Slicer1 = SUMMARIZE(Project,Project[Start Date]) Slicer2 = SUMMARIZE(Project,Project[Start Date])
Put the Start Date field of both Slicer tables into both slicers.Slicer1 is used to select a date range and the slicer2 is used to select a specific date.
Then create a measure.
Measure = IF(SELECTEDVALUE(Project[Start Date]) IN VALUES(Slicer1[Start Date]) || SELECTEDVALUE(Project[Start Date]) IN VALUES(Slicer2[Start Date]),1,0)Then place this measure in the visual containing the data as well as in the filter, setting the condition to is 1
Then you can see past and future projects.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hello domi25 ,
Please follow the below stesp.
Step1 - Please create calendar Table()
DateTable = CALENDAR(MIN(Projects[project start date]), MAX(Projects[project start date]))
Step2 - Use a DAX formula to create a new column that classifies the date into periods
Period =
IF('DateTable'[Date] < TODAY(), "Past",
IF(YEAR('DateTable'[Date]) = YEAR(TODAY()) && 'DateTable'[Date] >= TODAY(), "Current","Future"))Step3 - Add a calculated column that formats the date into a "Month-Year" format
MonthYear = FORMAT('DateTable'[Date], "MMM YYYY")
Step4 - Link your date table to your Project table by dates.
Step5 - In your Power BI report, use slicers for both Period and Month + Year from the datetable to filter the data you want
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
The solution you presented works exactly like mine. What I meant was an answer to the question of whether the kind of filtering I want is possible. I want to be able to select (in your solution) 'FUTURE' in one slicer and 'January 2023' in another, and I want to see all those projects. However, with this solution, they are mutually exclusive, and records not appear because January 2023 is not in the future. What I want is to see all future projects and that one from the past."
Thanks for dharmendars007 's reply ,please allow me to provide another insight.
Hi domi25 ,
Please try the following steps.
Create two slicer table.Slicer1 = SUMMARIZE(Project,Project[Start Date]) Slicer2 = SUMMARIZE(Project,Project[Start Date])
Put the Start Date field of both Slicer tables into both slicers.Slicer1 is used to select a date range and the slicer2 is used to select a specific date.
Then create a measure.
Measure = IF(SELECTEDVALUE(Project[Start Date]) IN VALUES(Slicer1[Start Date]) || SELECTEDVALUE(Project[Start Date]) IN VALUES(Slicer2[Start Date]),1,0)Then place this measure in the visual containing the data as well as in the filter, setting the condition to is 1
Then you can see past and future projects.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
