Calculate Working Day Target from Annual Target

[Tob_P]

Hi,

I'd like to calculate a working day target based off an annual target. I have managed to calculate a daily sales target based on calendar days in each FY by creating a custom date column and the following custom column...

This works well but I'm not stuck on breaking that down to working days only. I have two columns that could be used to determine this - either Day of week is >=0 <4 or Working Day Column is not null

I would really want to do this calculation in Query Editor. Can someone guide me as to if this is achievable please?

Thanks

[Tob_P · Accepted solution]

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.

[Manoj_Nair]

@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

[foodd]

@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"

[Tob_P]

Thanks for this but the file was added in another post. You have just missed it.

[Tob_P]

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.

Link to stripped back file

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?

[ToddChitt]

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.

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.