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
Anonymous's avatar
Anonymous
Not applicable
6 years ago

I applied your suggestion.

 

But I need something like this:

 

For week 12 (letter A above): for those items without resolved_date (status 'open') I need to consider the end date of the week (22/03/20) as 'resolved_date' but it is necessary to apply an unique rule for each week (13, 14, and so on).

 

My last DAX code:

 

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 (
IF(ISBLANK('MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE].[Date]);TODAY()-SWITCH(WEEKDAY(today();2);1;1;2;2;3;3;4;4;5;5;6;6;7;0);'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE].[Date]);'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE].[Date];
DAY
)

)
)

 

 

 

 

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
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
    Not applicable
    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