loop function to get free dates

[ANBILY1]

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: supplier and ranking to determine their position for audit.
In the auditor table, I have a column with the date one year in the future, that is, from today to 365 days, and 18 more columns 
corresponding to each auditor. I need to select a pair of auditors for each supplier using both tables that meet the following rules:
If auditor A and auditor B have 5 free days and the state is the same between the auditors, and the supplier returns Yes; otherwise, it 
returns No;
Return the smallest range above, that is, the date that starts first and has 5 days;
After the previous steps are met, the auditors are placed on reserve and the respective range is unavailable.
Any contribution is welcome.

Other important points:
- Weekends and holidays must be removed. The auditor table has a holiday column. Municipal holidays count as time off for auditors who 
live in the location of the holiday. National holidays, on the other hand, have a column indicating that their respective dates cannot be 
used for auditing.
- State is the geographic location that must match. There is another table with the auditor's domicile.

Above, an example of the auditor scheduling table, following the table logic:
- The date column is today plus 364 days, that is, one year in the future;
- It contains holiday columns;
- The remaining columns are the codes representing each auditor. The header is the auditor's code, and in the rows,
the supplier code, indicating that they already have an audit scheduled. The blank spaces are the available dates
for new audits. In other words, auditors who have coinciding free dates and the same domicile and state of residence are
authorized to audit the supplier, and so on.
- After verifying that the authorized pair is available, complete the table and search for the next pair until
the list of suppliers is exhausted.

Above is the supplier table. The supplier is represented by a code and has a state that must be the same as the state of the pair of auditors.

Above is the table with the auditors column, with each code in each row representing an auditor. The other column is the auditor's state of residence, which must be the same for both auditors in the pair and the same for the supplier.

Above is the table with the auditors and their respective municipal holidays, that is, it may be a municipal holiday for a certain 
auditor and not for another.

I know this is a complex issue, thanks again for your help.

[v-dineshya]

Hi @ANBILY1 ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,

Dinesh

[ANBILY1]

Dear, follow the information as requested:

Auditor	State
B94V	SE
DPEW	SP
DQMW	SP
EMJJ	SP
F5CD	RJ

Above is the table with the auditors column, with each code in each row representing an auditor. The other column is the auditor's state of residence, which must be the same for both auditors in the pair and the same for the supplier.

Supplier	State
10006087	RJ
10007454	SP
10008452	PR
10022612	BA
10025681	SP

Above is the supplier table. The supplier is represented by a code and has a state that must be the same as the state of the pair of auditors.

Date

03/09/2025

04/09/2025

05/09/2025

06/09/2025

07/09/2025

08/09/2025

09/09/2025

10/09/2025

The table above contains data corresponding to dates from today to one year in the future, that is, today plus
364 days. Each auditor will be entered in a column, and their respective audits will be filled in with the code
of the supplier whose audit is scheduled.

Auditor	Municipal holiday	State
B94V	08/07/2026	SE
B94V	08/12/2025	SE
B94V	17/03/2026	SE
B94V	24/06/2026	SE
DPEW	09/07/2026	SP
DPEW	28/02/2026	SP

Above is the table with the auditors and their respective municipal holidays, that is, it may be a municipal
holiday for a certain auditor and not for another.

Date

National holiday

Municipal Holiday

DPEW

DQMW

EMJJ

F5CD

FQ3C

FQR6

FWXE

G1TD

G323

G495

03/09/2025

10006087

04/09/2025

10006087

05/09/2025

10006087

06/09/2025

10006087

07/09/2025

Holiday

10006087

10007454

08/09/2025

Holiday

10007454

09/09/2025

10007454

10/09/2025

10007454

10008452

11/09/2025

10007454

10008452

12/09/2025

10008452

13/09/2025

10008452

14/09/2025

10008452

15/09/2025

16/09/2025

17/09/2025

Above is a sample of the results. In other words, the results table allows you to see which suppliers have scheduled audits, which auditors will conduct the audit, and the auditors' free dates for scheduling future audits.

The goal is to ensure that no supplier in the supplier list is left without a scheduled audit.

Thank you in advance for your help and attention.

[v-dineshya]

HI @ANBILY1 ,

Thank you for providing sample data. Please refer below output snap and attached PBIX file.

I have created M code . Please refer the Query editor.

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

Regards,

Dinesh

[Nabha-Ahmed]

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.