Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
MiklosSz
Frequent Visitor

Filter data table based on today

Hello community,

 

I'd like to ask for help since I just started using PowerBI.

 

I need to filter my data table for charts - or mark specific rows. The filter should be for ongoing works and works starting within the next two weeks. I tried  the embedded filters but "today" should be changing every day.

 

This is a sample table:

MiklosSz_0-1725176010280.png

Where and how can I filter this based on the above mentioned criteria?

 

Thank you for the help.

 

1 ACCEPTED SOLUTION
MiklosSz
Frequent Visitor

Dear @DataNinja777 and @Ashish_Mathur,

 

Based on your input and and examples I managed to study the problem and the logic of DAX for which I am tremendously grateful.

 

Here is how I solved the problem, and seems to be okay, but requires further testing and possible adjustments:

 

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.

View solution in original post

4 REPLIES 4
MiklosSz
Frequent Visitor

Dear @DataNinja777 and @Ashish_Mathur,

 

Based on your input and and examples I managed to study the problem and the logic of DAX for which I am tremendously grateful.

 

Here is how I solved the problem, and seems to be okay, but requires further testing and possible adjustments:

 

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.

DataNinja777
Super User
Super User

Hi @MiklosSz ,

 

Is your desired output a filter for only the work starting today, or for work starting in two weeks, including today? Your sample data covers the latter scenario. Assuming you want the result to filter only today's data, I am providing the solution below.

There are multiple ways to achieve your desired output. One approach is to create a calculated column that identifies today's date in your calendar table, which is related to the start date. Since you are focused on filtering data that starts today rather than filtering activities over a duration, I have created a relationship between your fact table (containing tasks) and the calendar table. While the standard method for more flexible duration analysis would be to use disconnected tables, this approach meets your specific requirements.

Today = switch(True(),[Date]=today(),"Today",
[Date]>today(),"Future",
[Date]<today(),"Past")

Your calendar table now includes a "Today" field to dynamically identify the current date.

DataNinja777_0-1725180494371.png

You can then filter the task field using the "Today" field in the calendar table, as shown below:

 

DataNinja777_1-1725180573402.png

I have attached an example pbix file for your reference.

Best regards,

Hi @DataNinja777 ,

Thank you very very much for the detailed description it cleared up some questions in my mind and some how-to s.

 

Regarding the "Today" coloumn

- ongoing works would be those tasks that starts "today" AND "start date < today < due date" - tasks that has started in the past but due date is not yet today 

- instead of "future" I would like to limit for two weeks only (I have 1200+ tasks,  up to 3,5 years), aka something like "today+1 < (soon) < today+14"

 

These logical parameters - combining with your first reply - will let me finish the charts for this section.

 

Later on I intend to publish it to online I assume these pre-defined connections will also work?

(online is needed so I can link it with Power Automate to refresh charts on days I specify - making it independent from my laptop)

 

Best regards, 

Hi,

I have solved a similar problem in the attached file.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.