Calculating backwards X days excluding weekends and holidays in Power BI Dax (Calculated Column)

[Anonymous]

Need help with calculating backwards X days excluding weekends and holidays in Power BI Dax (Calculated Column.)  I can get to a solution when I need to count forward X days excluding weekends and holidays.  It's the counting backwards where I run into problems.  Especially if the time periods cross over both weekends AND holiday(s).  I have a date table that includes the following columns:

• Date
• HolidayFlag (True/False)
• WorkdayFlag (True/False)
• WeekendFlag (True/False)
• IsWorkday (1=True,0=False)

I also have a table that includes Due Date.

If I needed to calculate 2 working days before the Due Date would return:

Due Date          2 Work Days Before

11/26/2019      11/22/2019

11/27/2019      11/25/2019

11/28/2019      11/26/2019

11/29/2019      11/26/2019

11/30/2019      11/26/2019

12/1/2019        11/26/2019

12/2/2019        11/26/2019

12/3/2019        11/27/2019

12/4/2019        12/2/2019   

Date Table

Date Table

Thanks

[MartynRamsden]

Hi @Anonymous 

This solution relies on their being a relationship between your fact table and your Date table.

Try creating a WorkingDayIndex calculated column in your Date table:

WorkingDayIndex = 
VAR RowDate = 'Date'[Date]
VAR RowWD = 'Date'[IsWorkingDay]
VAR Index =
CALCULATE ( 
    COUNTROWS ( 'Date' ),
    'Date'[Date] <= RowDate,
    'Date'[IsWorkingDay] = 1
) + 1
VAR Result = 
IF ( 
    RowWD = 0,
    Index + 1,
    Index
)
RETURN Result

Then, in your fact table, create a calculated column as follows:

Date -2 Working Days = 
VAR SelDateWDIndex = RELATED ( 'Date'[WorkingDayIndex] )
VAR Result = 
CALCULATE ( 
    MAX ( 'Date'[Date] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[WorkingDayIndex]  = SelDateWDIndex -2
    )
)
RETURN Result

If you need this as a measure rather than a calcualted column, you can replace the RELATED function with SELECTEDVALUE.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[Anonymous]

Hi Martyn,

Thanks for your suggestion.  I haven't tried it yet.  However, I stumbled across a solution that has worked for me using RANKX and LOOKUPVALUE.  It probably does the same thing as your suggestion 😀

First, in my DateTable I need to determine the "Rank" for "Workdays" (Excluding Weekends and Holidays):

WorkDayRank =

IF('DateTable’ [WorkdayFlag]=TRUE,RANKX(FILTER('DateTable','DateTable'[WorkdayFlag]=TRUE),'DateTable'[DateValue],,ASC))

Now I can use the resulting "Rank" to calculate my Due Date Minus 2 working days without making any changes to my "Date Table” using LOOKUPVALUE in my Fact Table. By making N Variable, I can quickly change the number of days to count backwards.

Due Date Minus N WORKING Days =

Var N = 2

Return

CALCULATE(MAX('DateTable'[DateValue]),FILTER('DateTable','DateTable'[WorkDayRank]=LOOKUPVALUE('DateTable'[WorkDayRank],[DateValue], 'Fact Table'[DueDate])-N))

Hopefully, this will be useful to others and I'll also get a chance to experiment with your solution!  (But you know how it goes, once you find a solution that works . . .)

Regards,

Thomas