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 d...
Need Help
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

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