Forum Discussion

ANBILY1's avatar
ANBILY1
Frequent Visitor
11 months ago
Solved

loop function to get free dates

Dear all, thank you in advance for your collaboration. I have the following question: I have two tables, one for suppliers and one for auditors. In the supplier table, I have the following columns: s...
Need Help
  • v-dineshya's avatar
    v-dineshya
    10 months ago

    Hi  ANBILY1 ,

    Please refere below output snap and PBIX file.

     

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

     

     

     

Nabha-Ahmed's avatar
Nabha-Ahmed
Super User
11 months ago

Hi ANBILY1 

This type of scheduling is hard to solve with DAX alone because you need to check availability, match auditors by state, and “reserve” dates once assigned. The best approach is to do it in Power Query (or SQL) before loading the data:

  • Unpivot the auditor table so each row = [Date, AuditorId, SupplierId].

  • Build a calendar that removes weekends + national/municipal holidays.

  • Mark free vs booked days, then find 5-day consecutive free ranges.

  • Pair two auditors in the same state, match with the supplier’s state, and assign the earliest available window.

  • Once assigned, mark those dates as unavailable for the next supplier.

End result = a clean assignment table (Supplier, AuditorA, AuditorB, StartDate, EndDate) you can model in Power BI.