Formulas based on working days in a week

[Bratone]

Hello,

I am trying to create a report which will calculate some metrics for me based on the amount of working days in that particular week. My logic was to create a table like this one for every week, put 1 for working days and 0 for non-working and then do all the formulas (product, divide etc.) based on the sum of the working days (so basically 5 for weeks with 5 working days, 4 for weeks for 4 working days etc.). 

The problem is that for example, I have some variables where I don't have data for all the working days, it may happen that the week has 5 working days and the variable only has data for 2 working days, so he will do the calculations in relation with 2 and not with 5 .- see pic below where the number of working days should be 4 for all the variables:

 

Basically, if we take the first line, the variable called ACR ESCIU should still be divided by 4 an not by 1, but because it has data only for 1 day it divides it by 1. Any idea how can I fix this please ? Right now my formula is : 

OTP Annotation HC = [Total Annotation] / [Working Weekdays] / 7.5 where Working Weekdays = sum('Process Target'[Working days])

Another problem I also have using these way of trying to do calculations is also related to the number of working days, basically I have this formula for the target :

OTP target volume = SUMX('Process Target', 'Process Target'[OP target shrinkage buffer] * [OTP HC] * [Working Weekdays]) 

If we look at the above pic, for the variable GSR it should be 4 * the process target which is the same for every day (check first pic of this post) * OTP HC but because the OTP HC is also calculated based on the individual value for each working day (which is 1 like I explained above), the value is wrong. Basically the value for OTP HC from above for each working day should be divided by 4 and not by 1.

Do you have any ideas how can I fix this or if I can make it work ? The simples way to do it would be to replace in every formula the sum of working day with 4,5 etc or the number of working days in the week, but in order to have an accuratate history or traceability and to be able to distinguish automatically the weeks with different working days, I need to find a formula who will do this for me ...

Thank you.

[Bratone]

Hi @v-easonf-msft ,

Long story short, what I want is to find a way in which I can divide/split my calculated measures based on the number of working days in a week.

To take a random example, let's say I am a company which sold the X amount in Week Y, I want to be able to see the X split by every working day which is part of week Y (For example, Monday, Tuesday, Wednesday, Thursday, Friday).  The simplest solution would be to have X / 5 because most of the weeks have 5 working days, but what happens when we have working weeks of 4 or of 6 days ? The formula needs to change dynamically so I can have an accurate trend when I look in the past.

My solution was to create a separate lookup file as you can see in my initial post, where I assigned to every working week day in a week the value of 1. so for a 5 days working week, every working day has a value of 1 (Working weekdays =1 * 5 = 5). For a week with 4 working days, Working weekdays = 1 * 4 = 4.

This solution was wrong for 2 big reasons (also check my initial post for more information):

1. If for one or more than one day in that week I don't have any sales value, the total will divide by the reamining days which is not corect for me. (So if I don't have sales on Monday, the Sales will divide by 4 and not by 5, even if the week had 5 working days).

2. I can't see the values INDIVIDUALLY split by each working day, see the pic on my initial post. Because my solution was to assign 1 to every working day, the sales amount for each individual working day is the same as the total, since it is divided by 1 and not by the sum (5 etc.)

Hope I was more clear in my explanations now.

Thank you,

[v-easonf-msft]

Hi, @Bratone 

Not fully sure what you want. Can you share a sample file and expected result to further illustrate your question?

Please check if new function networkdays can help.

Best Regards,
Community Support Team _ Eason