calculations not working properly due to a condition applied in another calculation

[bbbt123]

Hello, I am in an odd situations.

I have created a calculations "Hours", it subtracts the working hours of the employees  - the work received. But if it is referring to past weeks it subtracts the actual volume of work received, if it is referring to upcoming weeks it subtracts the estimated volume we will receive (which is included in another calculations). All good, my calculation is working. The strange fact is that, when I create another calculations that is calculating the accumulated backlog using the "Hours" calculations it doesn't work properly and values are wrong. This is because of the conditions applied in Hours, I know because if I remove that condition (if it is in the past use the actual volume, if in the future use the estimated volume) it works. The calculations is below:

Hours =
var myDate = weeknum(now()) var new_date = min ( 'calendar'[WeekNum] )
var val = if (myDate >= new_date, 1, 0)
var com = [Employees hours] - [Volume 2020] var expt = [Employees hours] - Merge1[Expected Volume]
return if(val = 1,com, expt)

calculation that is not working:

backlog =
var backlog_ = CALCULATE( [Backlog running total in Date], ALLSELECTED('calendar'[WeekNum]))
var date_max = CALCULATE(max ( 'calendar'[WeekNum] ))
var date_min = CALCULATE(min ( 'calendar'[WeekNum] ), ALLSELECTED('calendar'[WeekNum]))
var val = CALCULATE(
[Hours] ,
FILTER(
ALLSELECTED ( 'calendar' ),
'calendar'[WeekNum] >= date_min && 'calendar'[WeekNum] < date_max
)
)
var final = backlog_ - val return
if (final < 0, BLANK(), final)

any idea? i tried different workarounds but nothing, if the condition in "Hours" is applied it won't calculate the values in the rights way. Any help is appreciated. thank you in advance

[v-lili6-msft]

hi @bbbt123 

If possible, could you please share your sample pbix file and your expected output.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[bbbt123]

Hi cannot share the file as there are sensitive data. I will rephrase it providing more details and the desidered output. I have a calculation that is working, and it is counting the hours each employee will have to work on accumulated backlog, see below:

Hours on backlog (weekly)=

var myDate = weeknum(now()) var new_date = min ( 'calendar'[WeekNum] )

var val = if (myDate > new_date, 1, 0)

var val2 = if (myDate = new_date, 1, 0)

var com = if(val = 1, 0, [Regular 2020] - [Volume 2020]) var expt = [Regular 2020] - Merge1[Expected Volume]

return if(val= 1, com, expt )

I have another calculations that is working , itis calculating the actual backlog to date

Backlog running total in Date =

CALCULATE(

    SUM('Merge1'[Backlog]),

    FILTER(

        ALLSELECTED('calendar'[Date]),

        ISONORAFTER('calendar'[Date], MAX('calendar'[Date]), DESC)

    )

)

I have to create another calculations "remaining backlog" as I need the output below:

WeekNum	Backlog running total in Date	Remaining backlog	Hours employees will work on backlog
5	63	63	0
6	63	82	-19
7	63	108	-26
8	63	57	51
9	63	37	20
10	63	4	33
11	63	0	32

Basically I have to add at the initial backlog (the static value "Backlog  running total to date") the values in columns resulting from the calculations "Hours employees will work on backlog", accumulating every week. If the value in the column "hours" is negative, it must be added because not only the employees won't be able to work on backlog but they will also accumulate more hours in the backlog. How can I achieve this?

[Tad17]

For one thing I don't recommend using now()

Instead you should use Today() and Date.add when necessary.

[bbbt123]

 I tried with today () but nothing changes, the thing is that if I use IF in the calculations, that calculations will work whereas any other calculations referring to the original one, will ignore the IF condition