Looping through a table to find a vaue

[Krankyboy]

Hi 

I want to pass into a function a date and number, (the number of workdays a task will take) from my fact table.  I have a second table which is  just rows of workdays and a second column with a value of 1 - this signifies that the associated date is a workday.  Any dates falling on a weekend or stat holiday have been removed.

Basically I want to loop N times through the table and return the date.

Thanks,

A

[Anonymous]

Hi @Krankyboy ,

Please try new a custom function:

let
    GetEndDate = (startDate as date, workDays as number) =>
        let
            filteredWorkDays = Table.SelectRows(Table, each [date] >= startDate),
            workDayList = List.Distinct(Table.Column(filteredWorkDays, "date")),
            requiredWorkDays = List.FirstN(workDayList, workDays),
            endDate = List.Last(requiredWorkDays)
        in
            endDate
in
    GetEndDate

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Omid_Motamedise]

I didnt catch your question but for applying loop, this video can be helpful

https://youtu.be/G8PRbWuDcmQ

[slorin]

Hi @Krankyboy 

Workdays_Table[Workday_Column]{List.PositionOf(Workdays_Table[Workday_Column], [date]) + [number] }
 

Stéphane

[Krankyboy]

Hi,

Thanks for the response.  But this is not the answer.  I should have added more info.

my workday table is just rows like the following;

date                 isWorkDay (always 1)

2025-01-02    1

2025-01-03    1

2025-01-06    1

2024-01-07    1

I want to pass in a task start date, say 2025-01-01 which takes 3 days,  I assume I would have to loop 3 times then return 2025-01-06.   That returned date will become the start date for the next task.  My fact table will have about 12 task starts per row along with 12 workdays.

[wdx223_Daniel]

=Table.ExpandListColumn(Table.AddColumn(YourFactTable,"Workday Date",each List.FirstN(Table.Skip(WorkdayTable,(x)=>x[Date]<[Date])[Date],[Number])),"Workday Date")