Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Multiple date difference and date count in a string

Hi, can anyone help me with implementing this?

 

In my Dashboard I have 4 columns:

 

A) Number of the issue

B) Person Handling the issue

C) Creation Date of the issue

D) Comments on the issue

 

Can you help me with one or multiple DAX to:

 

1. Count the total number of the Comments placed for each issue, counting only the ones placed by the Handling Person.

2. Calculate how much time has passed between Creation Date of the issue and the date of the first comment placed by the Handling Person. Basically it can also be date of handling person's comment minus date of creator's comment + it needs to exclude weekend days.

3. When we have a longer conversation between the Creator and Handling Person, calculate how much time has passed between each comment of the Handling Person.

 

Here is how each cell looks like

Comments :Comments Column(text in a row)

Creation Date: Creation Date Column

 

Appreciate your help.

8 Replies

  • truptis's avatar
    truptis
    Community Champion

    Hi Anonymous ,
    for spliting your each cell into 4 columns, you can use Split in your Transform data:

    After Splitting, click on group by:

    Use your Person Handling column. 

    Then create a column:
    Result = Calculate(Count(Comments), [Handling_Person] = EARLIER([Handling_Person), [issue_no] = EARLIER(Issue_no), ALL(tablename))

    By this, your 1st issue will be resolved.

    • Anonymous's avatar
      Anonymous
      Not applicable

      truptis , your solution to split the columns splits the text incorrectly. Are you sure that "/" should be the delimiter?

  • truptis's avatar
    truptis
    Community Champion

    Anonymous - You will need multiple splits.

    1st split with " - " delimiter (space before and after - ) 
    2nd split with "(E-mail address)" delimiter

    3rd split with "(Comment)" 

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak , but I don't need to grab the e-mail. The most important thing is that I need to calculate the date difference between comments published for an issue.

      • truptis's avatar
        truptis
        Community Champion

        Anonymous -> for calculation date difference you can use DATEDIFF(date, comparison_date,DAYS). This will return the difference in dates in no. of days. eg- datediff(03/04/2021,04/04/2021,DAYS) will return 1