Forum Discussion

ablackmor's avatar
ablackmor
Regular Visitor
4 years ago

Biweekly Payroll Allocation to Daily Expense

I'm looking at all the allocation answers on the forums, as well as the patterns in DAX Patterns (many thanks to marcorusso & AlbertoFerrari ). I still feel very new on learning curve of DAX. However, despite my research I don't see anyone who's answered what I'm trying to do. The problem set seems relatively straightforward.

I'm using a dates table connected to a payroll fact table loaded from .csv payroll files (ADP/Paychex/UKG). The payroll fact table loads payroll register files for biweekly pay periods, so my payroll fact table has an active relationship with Pay Period End Date connected to the my Dates table, with inactive relationships to the Check Date and Pay Period Begin Date. What I want to accomplish is recalculating the 14-day payroll fact table to the daily grain so my payroll expense calculates accurately across the entire model. Currently, if I select a 13-day range that excludes the Pay Period End Date, for example, my payroll expense shows zero until the date filter includes the Pay Period End Date. This particular model's payroll fact table covers the 14 days between check beg and end date, but some of our models have semi-monthly payroll as well. If I was looking at one week, or one month, I'd like to see payroll expenses based on a simple (no workday accomodation) % out of 14 days that applies to the selected date range, which could be one payroll file (i.e. one week or weekend) or multiple payroll periods (one month or quarter). There are so many patterns for other types of allocations, but I haven't seen one for a straightforward biweekly to daily that removes the filter context then re-applies to calculate the correct daily total to aggregate accurately for any selected custom date range or prepopuldated date dimension. I don't think using M to convert the original biweekly payroll files to a daily amount divided by fourteen makes sense, as the payroll fact table would increase in size 14x. Is there a virtual table approach to pretend like the original biweekly payroll fact table acts as a daily / 14 fact table instead? Maybe I'm overthinking it, as I was looking at the problem from the standpoint of first and last payroll in a standard monthly accrual presentation. A month like March could have 1/14 days accrued from the first payroll file for Pay Period End Date Mar 1, then add 2/14 days for the last Pay Period End April 12, as well as two full biweekly payroll files for Mar 2 - Mar 29, inclusive. Thus, March payroll could use four payroll biweekly files to aggregate accurately--two full and two partial.

2 Replies