Need column to return date in working days from today, from query table column.

[Glengle]

Apologies if this is basic, this is my first attempt at a BI project.

I have a table of stock items generated from a query from my company's warehouse management system.

I have a column with the purchase lead time in working days, I want to create another column that will give me the date in workdays from this lead time column.

For example the column might say 15 (days lead time) I want the column to return 27/05/2026 on that table line.

I've tried to find this online, but I see a lot of 'from this date to this date' dax formulas, maybe my google game isn't too good?

Any help would be really appreciated!

Glenn

[v-karpurapud]

Hi @Glengle 

Just checking in as we haven't received a response to our previous message. Were you able to review the information above? Let us know if you have any additional questions.

Thank You.

[v-karpurapud]

Hi @Glengle 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to  @Olufemi7 , @mickey64 , @ryan_mayu and @danextian  for sharing helpful suggestions.

Could you let us know if the suggested solution resolved your issue? If not, please share any additional details so we can assist further.

Best regards,
Community Support Team.

[Glengle]

You guys are amazing! 

I really appreciate the feedback and the quick replies. 

I will go through these, but scanning through I'm not sure If I explained it right.

I might be thinking too much with my excel hat on, but I don't have a date column on my table

This is my table and I was looking to add a column on the end that displays the date from today, calculated in working days from the number in the lead time calculation column. This needs to change automatically each day, similar to =WORKDAY(TODAY(),G1) in excel.

Forgive me if I'm wrong many of the answers look like they're returning the day count rather than the date?

Like I said this is my first attempt, apologies if I'm reading the answers wrong.

[Ashish_Mathur]

Hi,

The same WORKDAY function exists in PowerBI as well.

[danextian · Accepted solution]

Try either of the following:

Workday 20 From Today = 
VAR _today =
    TODAY ()
VAR _workdays =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( _today + 1, _today + 30 ),
            "@Workday", WEEKDAY ( [Date], 2 ) <= 5
        ),
        [@Workday]
    )
RETURN
    MAXX (
        TOPN ( 20, _workdays, [Date], ASC ),
        [Date]
    )

Workday 20 From Today2 = 
VAR _today =
    TODAY () 
VAR _weeks =
    QUOTIENT ( 20, 5 )
VAR _extra =
    MOD ( 20, 5 )
VAR _weekday =
    WEEKDAY ( _today, 2 )
VAR _days =
    _weeks * 7
        + _extra
        + IF ( _weekday + _extra > 5, 2, 0 )
RETURN
   _today + _days
 

[ryan_mayu]

@Glengle you can add a column

Column2 =

var _today=today()

var _index=MINX(FILTER('Date',_today<='Date'[Date]),'Date'[Index])

return maxx(FILTER('Date','Date'[Index]=_index+'Table'[lead time]-1),'Date'[Date])

this is calculated by today's date

[danextian]

Hi @Glengle 

SQL BI discussed that in detail in their vlog https://www.youtube.com/watch?v=2HkBbqxBzF0

[ryan_mayu]

@Glengle 

you can create date table in power query

let
    // Set your start and end dates
    StartDate = #date(2026, 1, 1),
    EndDate   = #date(2026, 12, 31),

   
    NumberOfDays = Duration.Days(EndDate - StartDate),

    // Create a list of dates
    DateList = List.Dates(
        StartDate,
        NumberOfDays + 1,
        #duration(1, 0, 0, 0)
    ),

    // Convert list to table
    DateTable = Table.FromList(
        DateList,
        Splitter.SplitByNothing(),
        {"Date"},
        null,
        ExtraValues.Error
    ),

    // Change type
    #"Changed Type" = Table.TransformColumnTypes(
        DateTable,
        {{"Date", type date}}
    ),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.DayOfWeek([Date], Day.Monday) < 5 then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

then create a measure

Column =

var _index=maxx(FILTER('Date','Table'[date]='Date'[Date]),'Date'[Index])

return maxx(FILTER('Date','Date'[Index]=_index+'Table'[lead time]-1),'Date'[Date])

pls see the attachment below

[mickey64]

Step 0: I use these data below.

Step 1: I make two calendar tables.

<Calendar>

    Calendar = CALENDARAUTO()

    Weekday = WEEKDAY([Date],2)

    

<WorkingDayCalendar>

    WorkingDayCalendar = FILTER('Calendar','Calendar'[Weekday]<6)

    ID = RANKX(ALL('WorkingDayCalendar'),'WorkingDayCalendar'[Date],,ASC)

    

Step 2: I add a relationship.

    

Step 3: I add these columns to DATA tabe.

    ID = RELATED(WorkingDayCalendar[ID])

    NextID = [Days lead time]+[ID]

    NextDay = LOOKUPVALUE(WorkingDayCalendar[Date],WorkingDayCalendar[ID],[NextID])

    

[Olufemi7]

Hello @Glengle,

You’ll need a Date table for this since DAX doesn’t really have a WORKDAY function like Excel.

Basically, you treat working days as Monday to Friday and ignore weekends when moving forward from today.

So if your lead time is 15 days, you’re just picking the 15th working day from today using that filtered calendar.

This is the usual way people handle working days in Power BI.

Microsoft docs:
WEEKDAY
NETWORKDAYS

[Deku]

Check out this video