Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How can I get an 'End Date' to use datediff function in case that case it is missing

I've created a new column DAYS_OPEN to get datediff between REPORTED_DATE and RESOLVED_DATE. But, sometimes we won't have RESOLVED_DATE due to STATUS of the item is "OPEN". For my project purposes ...
Need Help
Show and Tell
  • kentyler's avatar
    kentyler
    6 years ago

    I don't understand this 

    but it is necessary to apply an unique rule for each week (13, 14, and so on).

    if we can  get a session going today perhaps it will be clearer to me.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi kentyler 

    I'm thankful for you. Our call had super helpful for me to solve this problem.

    I'm posting below the solution we've discussed during the call.

     

    You really saved a lot of my time on this project.

    Sorry for the delay to reply the thanks for you and the community.

     

    DAYS_OPEN =
                           DATEDIFF(
                                             'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE].[Date];
                                              'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[Closed_date].[Date];
                                          DAY)
     
    Closed_date =
                             VAR date_missing = ISBLANK('MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE].[Date])
                             VAR day_number = IF(date_missing;WEEKDAY(
                                                                                                      'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE].  [Date]);
                                                                                                     'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE].[Date])
                              VAR days_to_add = 8 - day_number
                             VAR return_date = DATEADD(
                                                                            'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE].[Date];
                                                                            days_to_add;
                                                                              DAY)
                              VAR result = if(
                                                      date_missing;return_date;
                                                     'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE].[Date])

                                    RETURN result
kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

You could try using reported_date   that would mean, if there is no resolved date, assumed the resolved date is the end of the week in which it was reported.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

kentyler 

I've implemented this suggestion but the error is persisting.

 

I put return... it's necessary? Even it is not running well.

 

 

DAYS_OPEN =
IF (
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[STATUS] = "CLOSED";
(
DATEDIFF (
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE];
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE];
DAY
)
);
(
DATEDIFF (
VAR resolved_date = IF(ISBLANK( 'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE]);
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE].[Date] - WEEKDAY('MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE].[Date];2) + 7;
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE].[Date])

RETURN
'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE];
resolved_date;DAY
 
)
)
)
  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Can you post a sample powerbi file ? or do a screen share ?

    I can possible fix it in the morning.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      kentyler 

      Sounds good.  I can share my screen.

       

      Do you have start zoom account?

       

      What your time zone?

       

      My time zone

      BRT (Brasília Time)
      UTC/GMT -3 hours
      • kentyler's avatar
        kentyler
        Icon for Solution Sage rankSolution Sage
        6 years ago

        looks like you are 4 hrs ahead of me

        do you want to do something in the morning

        8 your time 12 my time ?