topic Re: Filtering data table based on Today +14 days in DAX Commands and Tips

Filtering data table based on Today +14 days

MiklosSz — Sat, 31 Aug 2024 13:22:12 GMT

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.

Re: Filtering data table based on Today +14 days

Rupak_bi — Tue, 03 Sep 2024 14:22:48 GMT

Hi,

If you are still searching for this to solve, Please share sample data.

Re: Filtering data table based on Today +14 days

christinepayton — Tue, 03 Sep 2024 18:03:26 GMT

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.

Re: Filtering data table based on Today +14 days

MiklosSz — Fri, 06 Sep 2024 08:16:34 GMT

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.