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

[Anonymous]

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 I need to show by line each week number, so I'm finding a function that I get the last day of each week (ex: week 12 (16 to 22/mar) I need to get the last 22/mar/20 as RESOLVED_DATE).

Someone can help me? Sorry if it seems basic question, I'm new using PBI.

See below my DAX for DAYS_OPEN column:

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('MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE];'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE];DAY)))

[kentyler · Accepted solution]

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.

[Ashish_Mathur]

Hi,

This calculated column formula works

=IF(ISBLANK(Data[Resolved date]),TODAY()-SWITCH(WEEKDAY(today(),2),1,1,2,2,3,3,4,4,5,5,6,6,7,0),Data[Resolved date])

Hope this helps.

[Anonymous]

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 · Accepted solution]

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 · Accepted solution]

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]

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]),
                                                  Data[Date] – WEEKDAY(Data[Date],2) + 7, 
                                                   'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[RESOLVED_DATE])
            'MEL_ITEMS_TEMPO_MÉDIO_WEEKLY'[REPORTED_DATE];
            resolved_date;
            DAY
        )

    )
)

I practice Slow BI

[Anonymous]

@kentyler 

I've got some errors.

What's data can I put in replacement of Data[Date]?

[kentyler]

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]

@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]

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

I can possible fix it in the morning.

[Anonymous]

@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]

looks like you are 4 hrs ahead of me

do you want to do something in the morning

8 your time 12 my time ?

[Anonymous]

8 my time is fine

[kentyler]

you got it, send me a meeting invitation 

i'm ken at 8thfold dot com

[Anonymous]

@kentyler  I sent you a meeting invite from zoom