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