View My Drafts
We are trying to solve for individual paycheck amounts given the YTD pay amount (ytd_wages_subject_to_FUTA) in the Paycheck Trial measure. We then need to adjust the granularity up a level to sum all of the paycheck amounts for a given time period, in this case FY 2020 from January - December. We cannot rely on the YTD wages column because sometimes the FYE does not fall on the calendar year end or we would like to aggregate on the employee's address rather than each individual pay date.
We are experiencing the problem outlined in this post from SQLBI but have not been able to successfully adjust our DAX measure to solve our problem: https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/
We need the Paycheck Trial measure to provide an accurate sum of all of the individual paycheck amounts in the subtotal for the Employee on the matrix visual. For example, Oscar the Grouch is showing a sum of $2,520,100 for FY 2020 when it should total all of the individual check amounts in the Paycheck Trial column totaling $100,600.
We included sample data for 3 employees for FY 2020 with December paychecks from 2019 as well to test if we can adjust the time frame and still come up with an accurate summation at the matrix level of wages per employee.