Forum Discussion
Filter data table based on today
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.
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.
You can then filter the task field using the "Today" field in the calendar table, as shown below:
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.
