Datediff function with filter

[Anonymous]

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

[Anonymous]

Hello @Anonymous,

do you have an idea in this case?

Thanks in advance and best regards,

Christopher

[Anonymous]

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]

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]

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]

Hi @Anonymous,

thanks for the fast reply, but I got the same error message like before. If I use for VAR _shipping the OR formula, to exclude blank values in the receive-date column as well, it doesn´t worl as well:

Datediff except holiday and weekend =
VAR _shipping =
IF (OR(
HistoryReport[SHIPPING].[Date] <> BLANK ();HistoryReport[RECEIVE].[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 ) )

[Anonymous]

HI @Anonymous,

Since I'm not so clear for your data, can you please share some sample data with expected result for test and modify formula?

In addition, I also modify formula to check both start date and enddate to use static value to replace blank value:

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

In addition, holiday list is not necessary, if your table not contains date ranges which need to except with calculation, you can remove that part and direct calculate countrows ( _calendar ).

Regards,

Xiaoxin Sheng