Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Datediff function with filter

Hello everyone,

 

I want to create a measure or calculated column with turnaround times for some tools. I have one table with the listed tools, the date when we received the tool and the shipping date when the tool is repaired and shipped to the customer. When there is a tool which we had received and not shipped out yet, then there is a "." in the row of the shipping date.

 

Now I used the DATEDIFF function like this:

TAT_Total = DATEDIFF(HistoryReport[RECEIVE].[Date];HistoryReport[SHIPPING].[Date];DAY)

 

Now my problem is that I have the holiday days and the weekend in this formula and want to filter it out. For this I have loaded a date table for different countries, that means that one date is not unique because it appears for every country. In this table you can also see if the date is a holiday date or on a weekend with two different rows (IsWeekend = True or False; IsHoliday = True or False).

 

Furthermore when there is a dot in the shipping date, the turnaround time should be zero, until we have a shipping date.

 

Now I am not able to create the function to use the formula above and filter the weekend and holidays from the second table out. Can you help me to build this formula?

 

Thanks in advance!

 

Br

Christopher

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI Anonymous,

     

    I'd like to suggest you use countrows with calendar and except functions to calculate datediff.

     

    Sample:

    Datediff except holiday and weekend=
VAR _calendar =
    FILTER (
        CALENDAR ( HistoryReport[RECEIVE], HistoryReport[SHIPPING] ),
        WEEKDAY ( [Date], 2 ) <= 5
    ) //calendar wihtout weekend
VAR _holidayList =
    VALUES ( Holiday[Date] ) //holiday date list
RETURN
    COUNTROWS ( EXCEPT ( _calendar, _holidayList ) )

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hello Anonymous,

       

      first thanks for you fast help. I used the formula in my calculated column, but I can´t use it because for the CALENDAR function the start and end dates shouldn´t have any empty rows. 

       

      I´m sorry, I looked up the wrong column, instead of a dot there is no value for the column SHIPPING when the tool is not shipped yet. But a dot or a blank value should be the same result I guess.

       

      That´s the formula I used from you:

      Datediff except holiday and weekend =
      VAR _calendar =
      FILTER (
      CALENDAR ( HistoryReport[RECEIVE].[Date]; HistoryReport[SHIPPING].[Date] );
      WEEKDAY ( [Date]; 2 ) <= 5
      ) //calendar wihtout weekend
      VAR _holidayList =
      VALUES ( ySpiDateDimension[Date].[Date] ) //holiday date list
      RETURN
      COUNTROWS ( EXCEPT ( _calendar; _holidayList ) )

       

       

      Do you have any ideas to get that result and filter the blank values out of the calculation? 

       

      Thanks and best regards

      Christopher

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Anonymous,

         

        For blank shipping date, you can setting a static value to replace it.(eg. today function)

        Datediff except holiday and weekend =
VAR _shipping =
    IF (
        HistoryReport[SHIPPING].[Date] <> BLANK ();
        HistoryReport[SHIPPING].[Date];
        TODAY ()
    )
VAR _calendar =
    FILTER (
        CALENDAR ( HistoryReport[RECEIVE].[Date]; _shipping );
        WEEKDAY ( [Date]; 2 ) <= 5
    ) //calendar wihtout weekend
VAR _holidayList =
    VALUES ( ySpiDateDimension[Date].[Date] ) //holiday date list
RETURN
    COUNTROWS ( EXCEPT ( _calendar; _holidayList ) )

        In addition, you can also consider to use if statement to check blank result to replace calculation result with static value.

         

        BTW, current power bi not support create dynamic calculated column/table based on slicer/filter.

         

        Regards,

        Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello Anonymous,

     

    do you have an idea in this case?

     

    Thanks in advance and best regards,

    Christopher