Forum Discussion

PC2022's avatar
PC2022
Helper III
1 year ago
Solved

Identify tasks with current date

I need to write dax that will identify tasks that are taking place as of today (from the example below it should only be "JPA". If the task has been 100% complete, it shouldn't be included in the result. I am not sure what function to use. Thank you!

 

 

  • FreemanZ's avatar
    FreemanZ
    1 year ago

    Hi PC2022 ，

     

    try to write a measure like:

    Count = 
COUNTROWS(
    CALCULATETABLE(
        VALUES(data[Name]),
        FILTER(
            data,
            data[start]<=TODAY()
                &&data[finish]>=TODAY()
        )
    )
)

or 

List = 
CONCATENATEX(
    CALCULATETABLE(
        VALUES(data[Name]),
        FILTER(
            data,
            data[start]<=TODAY()
                &&data[finish]>=TODAY()
        )
    ),
    data[Name],
    ", "
)

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    Hi PC2022 ，

     

    try to write a measure like:

    Count = 
COUNTROWS(
    CALCULATETABLE(
        VALUES(data[Name]),
        FILTER(
            data,
            data[start]<=TODAY()
                &&data[finish]>=TODAY()
        )
    )
)

or 

List = 
CONCATENATEX(
    CALCULATETABLE(
        VALUES(data[Name]),
        FILTER(
            data,
            data[start]<=TODAY()
                &&data[finish]>=TODAY()
        )
    ),
    data[Name],
    ", "
)
  • PC2022's avatar
    PC2022
    Helper III
    1 year ago

    Thank you! My coworker helped me come up with the formula. I had to turn the project in before i heard back from here. I appreciate your help!

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi PC2022 

       

      Glad to hear you solved the problem! Could you please mark the solution as an ANSWER? This will help more users who are facing the same or similar difficulties. Thank you!

       

      Best Regards,
      Yulia Xu

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    Hi PC2022 

    To create a DAX measure to identify tasks that are active as of today (and are not 100% complete), you can use a combination of IF, AND, TODAY, and filtering functions like FILTER to isolate the tasks that meet your criteria. Assuming you have columns for task start and end dates, as well as a completion status, here’s how you can approach it.

    Suppose:

    • Start Date column represents the start date of the task.
    • End Date column represents the end date of the task.
    • Completion column represents the task completion percentage.
    Active Tasks =
IF (
    AND (
        TODAY () >= [Start Date],
        TODAY () <= [End Date],
        [Completion] < 100
    ),
    1, // Indicating that the task is active
    0 // Not active or 100% complete
)

    How to Use This Measure

    You can add this measure to a table visual and then filter it to only show rows where the measure equals 1. This should allow you to see only tasks that are currently in progress as of today and not yet completed.

     

    Did I answer your question? Mark my post as a solution, this will help others!

    If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

    Kind Regards,
    Poojara
    Data Analyst | MSBI Developer | Power BI Consultant
    YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS