Forum Discussion

IvanS's avatar
IvanS
Helper V
3 years ago
Solved

Select close date from the latest created task

Hi guys,

 

I would need your help with adjusting the following function. I have 2 tables - FACT_Tasks and FACT_Cases. There is relation between field "RelatedTo_ID" in FACT_Tasks and "ID" column in FACT_Cases. There might be several tasks related to one case ID.

 

Originally, the function is selecting latest closed task groupped under one case ID. However, the business requirement has changed and now I need to show close date of latest created task within the particular case.

 

Last task (Closed_Time) = 
MAXX(
    FILTER(FACT_Tasks, FACT_Tasks[RelatedTo_Id] = EARLIER(FACT_Cases[Id]) && FACT_Tasks[Team] in {"Team 1", "Team 2"} && NOT FACT_Tasks[Status] in {"Canceled"}), 
        FACT_Tasks[ClosedTime (Time)])

 

Could you please help me with adjusting the formula?

Thank you
IvanS

Need Help
Tips and Tricks
  • IvanS's avatar
    IvanS
    3 years ago

    Finally, I have managed to calculate this using nested MAXX function:

     

    Close date of latest created task within case = 

var _maxcreateddate = MAXX(
    FILTER(
        FACT_Tasks, 
        FACT_Tasks[RelatedTo_Id] = EARLIER(FACT_Cases[Id]) && 
        FACT_Tasks[Team] in {"Team1", "Team2"} && 
        NOT FACT_Tasks[Status] in {"Canceled"}
        ), 
        FACT_Tasks[CreatedTime (Time)]
)
RETURN

MAXX(
    FILTER(
        FACT_Tasks, 
        FACT_Tasks[RelatedTo_Id] = EARLIER(FACT_Cases[Id]) && 
        FACT_Tasks[Team] in {"Team1", "Team2"} && 
        NOT FACT_Tasks[Status] in {"Canceled"} &&
        FACT_Tasks[CreatedTime (Time)] = _maxcreateddate
        ), 
        FACT_Tasks[ClosedTime (Time)]
)

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    IvanS , Try like( Based on what I got) 

     

    Latest created task (Closed_Time) =
    MAXX(
    FILTER(
    FACT_Tasks,
    FACT_Tasks[RelatedTo_Id] = EARLIER(FACT_Cases[Id]) &&
    FACT_Tasks[Team] IN {"Team 1", "Team 2"} &&
    NOT FACT_Tasks[Status] IN {"Canceled"}
    ),
    FACT_Tasks[CreatedTime (Time)]
    )

    • IvanS's avatar
      IvanS
      Helper V
      3 years ago

      Hi amitchandak ,

       

      I tried this function and unfortunately it is selecting latest created time.

       

      I am putting below example with explanations - I am attaching also tables for testing purposes.

      I need the red highlighted datetime which is defined as close date of latest created task.

       

      FACT_Tasks     
      Task IDTeamRelatedTo_IDCreatedTimeClosedTimeNote
      1Team1130.3.202315.4.2023This is first created task so we took created time from it
      2Team111.4.202330.5.2023This task was closed as latest BUT it was created before Task Nr. 4 so it is out of scope)
      3Team311.5.20231.6.2023This is different team which is out of scope
      4Team1115.4.202316.4.2023This is the latest created task so we took closed time from it
      5Team121.4.2023 This is not yet closed (closed time is blank)



      FACT_Cases  
      Case IDFirst task datetimeLast task datetime
      130.3.202316.4.2023
      21.4.2023(blank)

       

      Hope it is more clear now.

      Thank you

      IvanS

  • BiNavPete's avatar
    BiNavPete
    Resolver III
    3 years ago

    Hi IvanS 

    I think you just need to change the last piece of the DAX to ClosedDate.

    Last task (Closed_Time) = 
MAXX(
    FILTER(FACT_Tasks, FACT_Tasks[RelatedTo_Id] = EARLIER(FACT_Cases[Id]) && FACT_Tasks[Team] in {"Team 1", "Team 2"} && NOT FACT_Tasks[Status] in {"Canceled"}), 
        FACT_Tasks[ClosedDate])

     

    The issue may be however that you don't have a ClosedDate column in FACT_Tasks.
    I'm guessing that FACT_Tasks[ClosedTime (Time)] has a datetime data type in power query. If it is then it can be transformed into two columns one for date and one for time. Highlight the FACT_Tasks[ClosedTime (Time)] then on the transform ribbon take the Date | Date Only option.

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happy to help!!

    Pete
    Web: https://binavigation.com
    Linked In: https://www.linkedin.com/in/pete-smith-955b73181

     

    • IvanS's avatar
      IvanS
      Helper V
      3 years ago

      Hi BiNavPete ,

       

      I have both fields ClosedDate (as date) and ClosedTime (as datetime). I need to get ClosedTime (as datetime) as result.

       

      Your adjustment is returning the latest close date within case which is not the result I am looking for. I would need close date of lastest created task.

       

      IvanS

  • IvanS's avatar
    IvanS
    Helper V
    3 years ago

    Finally, I have managed to calculate this using nested MAXX function:

     

    Close date of latest created task within case = 

var _maxcreateddate = MAXX(
    FILTER(
        FACT_Tasks, 
        FACT_Tasks[RelatedTo_Id] = EARLIER(FACT_Cases[Id]) && 
        FACT_Tasks[Team] in {"Team1", "Team2"} && 
        NOT FACT_Tasks[Status] in {"Canceled"}
        ), 
        FACT_Tasks[CreatedTime (Time)]
)
RETURN

MAXX(
    FILTER(
        FACT_Tasks, 
        FACT_Tasks[RelatedTo_Id] = EARLIER(FACT_Cases[Id]) && 
        FACT_Tasks[Team] in {"Team1", "Team2"} && 
        NOT FACT_Tasks[Status] in {"Canceled"} &&
        FACT_Tasks[CreatedTime (Time)] = _maxcreateddate
        ), 
        FACT_Tasks[ClosedTime (Time)]
)