I'm not sure how I'm going to explain this, but I will sure try. I am creating a Pay Schedule in PQ (because I can :)) and I have got it (almost) perfect! The only problem I have in future proofing my calendar is when a pay day and a holiday land on the same day.
I have made an offset count of pay day and holiday working days for when pay day lands on a weekend. I accomplished this by merging columns then grouping the weeks together to create a table then manipulating the column tables to filter out empty tables and finally creating an index column to find an offset of days. That works unless Pay Day and a holiday land on the same day to begin with (as in 2022). Y'all, my creativity maxed out. Anybody have any ideas? Calendar attached.
I know this is a horrible explanation, I'm sorry, hopefully once you see it will make more sense.
Hi Jessica,
How about if you added a conditional column that checked if the Event column contained Pay Day AND the length of the text in Event was > 7 characters, then Working Date -1 else Working Date?
=if Text.Contains([Event], "Pay Day") and Text.Length([Event]) > 7 then Date.AddDays([Working Date], -1) else [Working Date]
You'll have to fix the issue where New Year's Day Working Date is currently, blank, but otherwise it's ok.
Mynda
Mynda! Thank you so much! You're creativity reigns supreme!
