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,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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