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.
Tob_P- Please check this out, let me know if this works. If this fix your problem, please tick this a solution and a thumps up.
Calculate the Total Number of Working Days:
If you want to use the Day of week column:
Total Working Days =
COUNTROWS(
FILTER(
ALL('YourTableName'),
'YourTableName'[Day of week] >= 0 && 'YourTableName'[Day of week] < 4
)
)If you want to use the Working Day Column (assuming blank means it's not a working day):
Total Working Days =
COUNTROWS(
FILTER(
ALL('YourTableName'),
NOT(ISBLANK('YourTableName'[Working Day Column]))
)
)If your annual target is a single number (like a measure or a value in a single cell), you can directly divide it by the total working days. If it's a column, you need to first sum it up.
Working Day Target =
[Annual Target] / [Total Working Days]I've hypothetically established this DAX measure, but without much clarity regarding your datasets. If this doesn't serve your needs, I recommend you share a sample dataset like foodd suggested. This will give me a more comprehensive understanding of your data structure, enabling me to devise a more suitable solution. Cheers
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.
Tob_P , Manoj_Nair pointed out precisely why it is wise to always add you Power BI Desktop File to a Post.
"I've hypothetically established this DAX measure, but without much clarity regarding your datasets. If this doesn't serve your needs, I recommend you share a sample dataset like @foodd suggested. This will give me a more comprehensive understanding of your data structure, enabling me to devise a more suitable solution. Cheers"Thanks for this but the file was added in another post. You have just missed it.
