Forum Discussion
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 ...
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.
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
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
Solution Sage
Solution SageCan you post a sample powerbi file ? or do a screen share ?
I can possible fix it in the morning.