Forum Discussion
Filtering data table based on Today +14 days
Hello,
I'd like to ask for help since I just started using PowerBI and bumped into problem I cannot solve yet.
I can know a little bit of programming but this whole PowerBI/DAX is new to me (not sure if this is the correct topic even).
I have a data table streamed from sharepoint list which is a MS Project file basically.
For some charts I would need this table to be filtered based on the task start date/due date (format: 2025. 12. 18. 16:00:00), to see the current tasks ongoing and the upcoming work within two weeks.
Criteria 1: startdate < today < due date
Criteria 2: today < startdate < today + 14
This file is in PowerBI desktop for now but I intend to upload it online. (I read somewhere maybe DAX code will not go with it?)
Any help is appreciated, I'm kinda in the dark here.
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.
3 Replies
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.
Hi,
If you are still searching for this to solve, Please share sample data.
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.
