Forum Discussion
Filtering data table based on Today +14 days
Hello,
Sorry, this post was marked as spam for some reason so I tried at another forum.
The solution which worked for me is this:
When = switch(True(),[StartDate]=today(),"Today",[Startdate]>today() && [Startdate]<today()+14,"Soon",[Startdate]<today() && [DueDate]<today(),"Past",today()>[StartDate] && today()<=[DueDate], "Present",[Startdate]>=today()+14,"Future")This basically divides the past works, ongoing works, expected start of works "soon", and future works plus I added today's starting works as an added coloum to my data table.
You probably want some Power Query calculations to return true/false or some sort of text label that makes sense to filter on (e.g. a column for "starting soon" true/false, or "late" true/false to use as filters). You can do custom columns in Power Query in the toolbar, the calculations aren't super difficult but they are pretty strict about syntax - so you can add days to a date field, then compare it to today's date, and return something for different cases in an if-statement.
So for example, I have a report that uses task data that has a flag for overdue that looks like this:
if ([taskDue] <> null and [taskProgress] <> 100 and [taskDue] < DateTime.Date(DateTime.LocalNow())) then true else false
<> here means not equals, and taskDue is the name of the due date column, so if it has a due date and the progress isn't 100 (%... it's planner data, which isn't a decimal like you normally see) and the due date is less than today, then it's overdue. Then you can use that column in a filter/slicer. Date.AddDays() can add days to a date field for the comparison too.