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 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)))
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

14 Replies

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

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

      kentyler 

       

      I've got some errors.

       

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

       

      • 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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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'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.