Forum Discussion
Calculate Working Day Target from Annual Target
Thanks to those who gave DAX solutions to this but I wanted to solve it in Query Editor.
How I did it...
1. Merged my FY & Month columns
2. Grouped Date Column & new Merged column with an output of a new column with Operation of All rows and another new column with an Operation of Count Rows
3. Grouped the Merged column with a new column with an Operation of Count Rows
....and this then gave me the count of the number of working days within the month for month in the FY, allowing me to divide the target / number of working days.
Thank you for this but it's something that I'd like to see if I can do in Query Editor, with another applied step similar to the screenshot solution I have for calculating a daily total based on 365 days.
You will see that each Salesperson has an annual target that's been broken down into a daily target for each calendar day of this FY. So for Salesperson 1, currently the daily target is £7,056. Working on the basis that there are 263 working days in the FY 01/05/2023 - 30/04/2024, then I would want the annual target divided by the working days = £2,499,968 / 263 = £9505.58 per WORKING DAY but the output in my table would need to be...
...specifically, there should never be a target against a non-working day.
Hope that clarifies for anyone reading?
In your date table, add a column with a value of 1 if it is a working day, and 0 if not. Create a copy of this and aggregate it by Calendar Year, with a SUM of Working Days:
Year Working days
2022 252 (guessing)
2023 254
Join this to your Annual Budget table on Year. Join this result to the original Date table on Calendar Year. Now, every day will have in one row:
Working Day (1 or 0), Total number of working days in the year, total annual budget.
Daily budget = [Working Day] x [Annual Budget] / [Working Days in the Year]
For weekends, the [Working Day] = 0 so the result of above is 0.