Forum Discussion

deek2123's avatar
deek2123
New Member
5 years ago
Solved

Add working days to a submitted date

I am new-ish to Power BI and I am trying to display a column with the date that is 10 working days after a record is submitted. I already have a calendar table in my model and I have added some new columns to give me the base info I think I need:

 

Calc_Is Business Day = IF(WEEKDAY('dw Dim_Date'[Calendar_Date])=1 || WEEKDAY('dw Dim_Date'[Calendar_Date])=7 || NOT(ISBLANK('dw Dim_Date'[Is_Holiday])),0,1)
 
Calc_Rank Business Day = RANKX(FILTER('dw Dim_Date','dw Dim_Date'[Calc_Is Business Day]=1),'dw Dim_Date'[Calendar_Date].[Date],,ASC)
 
Calc_Plus 10 BD = LOOKUPVALUE('dw Dim_Date'[Calendar_Date],'dw Dim_Date'[Calc_Is Business Day],1,'dw Dim_Date'[Calc_Rank Business Day],'dw Dim_Date'[Calc_Rank Business Day]+10)
 
This works in my Calendar table and I am having trouble getting this to translate to my record table where I have a table.date_submitted_adjusted column that adjusts the date based on the time of submission. For example, if a record is submitted after 5PM then it shows an adjusted date of Date_submitted+1day. 
 
I would like to be able to display a column with my date_submitted_adjusted and then a column that shows a date that is +10BD. 
Need Help
Tips and Tricks
  • parry2k's avatar
    parry2k
    5 years ago

    deek2123 watch the video provided by mahoneypat or use the following expression to add a column that I used many times to achieve something similar, in this expression, we are calculating x number of days after the order date to get the ship date, you can replace the table and column name as per you model. The only thing is required is to have a "working day" flag in your date dimension.

     

     

    Shipping Date = 
VAR __numberofDays = 10
VAR __orderDate = Orders[Order Date]
VAR __dateTable = 
    CALCULATETABLE ( 
        VALUES ( 'Dim Date Table'[Date] ), 
        'Dim Date Table'[Is Working Day] = 1, 
        'Dim Date Table'[Date] >= __orderDate 
    )
VAR __dateTableWithRank = 
    ADDCOLUMNS ( 
        __dateTable, 
        "@Rank", RANKX ( __dateTable, [Date], , ASC, Dense ) 
    )
RETURN
MAXX ( 
    FILTER ( 
        __dateTableWithRank, 
        [@Rank] = __numberofDays + 1
    ), 
    [Date] 
)

     

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    deek2123 watch the video provided by mahoneypat or use the following expression to add a column that I used many times to achieve something similar, in this expression, we are calculating x number of days after the order date to get the ship date, you can replace the table and column name as per you model. The only thing is required is to have a "working day" flag in your date dimension.

     

     

    Shipping Date = 
VAR __numberofDays = 10
VAR __orderDate = Orders[Order Date]
VAR __dateTable = 
    CALCULATETABLE ( 
        VALUES ( 'Dim Date Table'[Date] ), 
        'Dim Date Table'[Is Working Day] = 1, 
        'Dim Date Table'[Date] >= __orderDate 
    )
VAR __dateTableWithRank = 
    ADDCOLUMNS ( 
        __dateTable, 
        "@Rank", RANKX ( __dateTable, [Date], , ASC, Dense ) 
    )
RETURN
MAXX ( 
    FILTER ( 
        __dateTableWithRank, 
        [@Rank] = __numberofDays + 1
    ), 
    [Date] 
)

     

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡