Forum Discussion
loop function to get free dates
Hi ANBILY1 ,
I am including .pbix file for your better understanding, please have a look into it:
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
Super UserPlease provide some sample data representing your problem set. Pasted in such that we can easily copy and paste into Power BI or Excel. Also, a snip or paste of the expected result.
A few clarifying questions, which probably would be answered with some sample data:
- Do all auditors start out with totally open dates? Or are there random reservations (scheduled vacation or something)?
- Can you define what, "state is the same between the auditors" means? Does this just mean they both have unreserved dates? Or is there a third auditor dimension that holds auditor locations (in case you means states like FL, NY, CO)?
Dear Sir, thank you for your interest in helping.
I am translating the text from Portuguese to English.
Regarding your questions: Answer 1 - Yes, it is necessary to remove weekends and holidays. The auditors' table
already has holiday columns. Municipal holidays already count as a busy date for each auditor residing in the
municipality with the holiday. National holidays, on the other hand, have a column indicating that their
respective dates cannot be used for auditing. 2 - State is the geographic location that must match. There is another table with the auditor's domicile
Above is a sample of the auditor schedule table, following the table logic:
- The date column is from today plus 364 days, that is, one year in the future;
- It has the holiday columns;
- The other columns are the codes representing each auditor. The header is the auditor's code, and in the rows,
the code repeats, indicating that they already have an audit scheduled. The empty spaces are the dates available
for new audits. That is, 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.
I know this is a complex issue, thanks again for your help.