Count number of days between a field value and today excluding weekends

[antonjamese]

I need to caclulate the number of days between a table field named Launch_time and today while removing weekends and holidays.

Todays date is not an available field in the table that contains Launch_time.  I keep getting hung up trying to use Today() as a new column because I cannot use it with DATEDIFF or DATESBETWEEN.  I do have a calendar table that contains a field for today's date.

Thanks for the help

[antonjamese]

Thank you for the responses.

I found the solution on the How To Power BI Youtube page Calculating the Number of Working Days in Power BI | Custom NETWORKDAYS Function using Power Query -....  He has a very good solution that is easy to create and modify.

[Anonymous]

Hi @antonjamese,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[antonjamese]

I cannot share the .pbix so I hope this will give you enough information.

I need to calculate the number of days between 'PRODUCT_ORDER_LINEUP'[LAUNCH_TIME] 

and 'FY Calendar'[Today] excluding weekends and holidays, when

'PRODUCT_ORDER_LINEUP' [COMPLETION TIME] is null.

Is Holiday and Is Working Day is in the FY Calendar table

Relationship between FY Calendar and PRODUCT_ORDER_LINEUP 

FY Calendar table

PRODUCT_ORDER_LINEUP table

[Anonymous]

Hi @antonjamese,

Sorry for the late response, you can try to use the following measure formulas to get the selected 'date range' fact table user workday count based current LINE_ID.

Count rows that really recorded in the table:

Measure =
CALCULATE (
    COUNTROWS ( PRODUCT_ORDER_LINEUP ),
    FILTER (
        ALLSELECTED ( PRODUCT_ORDER_LINEUP ),
        [LUNCH_TIME] <= TODAY ()
            && OR ( [COMPLETION TIME] <= TODAY (), [COMPLETION TIME] = BLANK () ) //date range filter
            && WEEKDAY ( [LUNCH_TIME], 2 ) <= 5 //weekend filter
    ),
    VALUES ( PRODUCT_ORDER_LINEUP[LINE_ID] ) //current line_ID filter
)

Workday count of date range except weekends:

Measure =
VAR _startDate =
    CALCULATE (
        MIN ( PRODUCT_ORDER_LINEUP[LUNCH_TIME] ),
        ALLSELECTED ( PRODUCT_ORDER_LINEUP ),
        VALUES ( PRODUCT_ORDER_LINEUP[LINE_ID] )
    )
VAR _endDate =
    CALCULATE (
        MAX ( PRODUCT_ORDER_LINEUP[COMPLETION TIME] ),
        ALLSELECTED ( PRODUCT_ORDER_LINEUP ),
        VALUES ( PRODUCT_ORDER_LINEUP[LINE_ID] )
    )
RETURN
    COUNTROWS (
        FILTER (
            CALENDAR (
                _startDate,
                IF ( _endDate <> BLANK (), MIN ( _endDate, TODAY () ), TODAY () )
            ),
            WEEKDAY ( [LUNCH_TIME], 2 ) <= 5
        )
    )

Regards,

Xiaoxin Sheng

[antonjamese]

Thank you for the help.  The count rows measure is working.  The workday count of range is not.  It is failing on line 21.  I have tried several different things but it is still not working. 

The field LUNCH_TIME should be LAUNCH_TIME.

Measure =

VAR _startDate =

CALCULATE (

MIN ( PRODUCT_ORDER_LINEUP[LAUNCH_TIME] ),

ALLSELECTED ( PRODUCT_ORDER_LINEUP ),

VALUES ( PRODUCT_ORDER_LINEUP[LINE_ID] )

)

VAR _endDate =

CALCULATE (

MAX ( PRODUCT_ORDER_LINEUP[COMPLETION_TIME] ),

ALLSELECTED ( PRODUCT_ORDER_LINEUP ),

VALUES ( PRODUCT_ORDER_LINEUP[LINE_ID] )

)

RETURN

COUNTROWS (

FILTER (

CALENDAR (

_startDate,

IF ( _endDate <> BLANK (), MIN ( _endDate, TODAY () ), TODAY () )

),

WEEKDAY ( [LAUNCH_TIME], 2 ) <= this is the line it fails on.

)

)

[Anonymous]

Hi@antonjamese,

I think the above issue is due to the new calendar table not including the 'LAUNCH _TIME' field, so it can't be used as filter conditions. For this scenario, you can try to use the below formulas that I modified if helps:

Measure =
VAR _startDate =
    CALCULATE (
        MIN ( PRODUCT_ORDER_LINEUP[LUNCH_TIME] ),
        ALLSELECTED ( PRODUCT_ORDER_LINEUP ),
        VALUES ( PRODUCT_ORDER_LINEUP[LINE_ID] )
    )
VAR _endDate =
    CALCULATE (
        MAX ( PRODUCT_ORDER_LINEUP[COMPLETION TIME] ),
        ALLSELECTED ( PRODUCT_ORDER_LINEUP ),
        VALUES ( PRODUCT_ORDER_LINEUP[LINE_ID] )
    )
RETURN
    COUNTROWS (
        FILTER (
            CALENDAR (
                _startDate,
                IF ( _endDate <> BLANK (), MIN ( _endDate, TODAY () ), TODAY () )
            ),
            WEEKDAY ( [Date], 2 ) <= 5
        )
    )

BTW, the above two expressions mean two scenarios to get the workday count. The first one considers the real records in your table; the second one only calculate on the particular date range:

Regards,

Xiaoxin Sheng

[antonjamese]

Thanks for your quick response.

I don't seem to be able to get this to work.  I will define it better.

In a table named Product_Order_Lineup I have a field named Launch_Date

In a table named FY Calendar I have a field named Today

In the FY Calendar table I also have the Is Holiday and Is Working day fields

What I need to be able to get is this

Calculate(DatesBetween = ('FY Calendar'[Today], Product_Order_Lineup[Launch_Date] and then exclude my weekends and holidays.

I am getting hung up because the fields I need are in 2 different tables.

[Sai4237]

Hi ,

You can try something like below : 

Add two flag columns to your Calendar table.

1. Is_Holiday_Flag --- Set to TRUE if holiday, False if not

2. Is_WeekDay_Flag ---- Set to TRUE if WeekDay, False if not

Now, Use the DAX formula as below:

CALCULATE(DATESBETWEEN(Calendar[Date],Calendar[Launch_Date],_MaxDate ),Calendar[Is_Holiday_Flag] = FALSE, Calendar[Is_WeekDay_Flag]=TRUE)

You can replace the _MaxDate  with the field that contains today's date. 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!     
Connect On LinkedIn