New Column based on Condition and Calculation

[Joe89]

Hello,

I am trying to create a line grap showing a "Value" per "Phase" and respective limits. the limits are being calculated based on the previous phase value. The value resulting out of the phases is depending on the phase outcomes so might change up/down/stay the same.

Phase Limit Rules:

Phase 1 => Value of P1 +/- 100% Limit

Phase 2 => Value P1 +/-50% Limit

Phase 3 => Value P2 +/-30% Limit

Phase 4 => Value P3 +/-10% Limit

Example Table of desired result. Phase and Value is what I have, Lower Limit and Upper Limit is what I am trying to create. I guess the solution for the two columns is in principle the same but with respective sign change in the calculation.

Project	Phase	Value	Lower Limit	Upper Limit
A	1	10	0	20
A	2	20	5	15
A	3	10	14	26
A	4	10	9	11
B	1	5	0	10
B	2	5	2,5	7,5
B	3	5	3,5	6,5
B	4	5	4,5	5,5
C	1	20	0	40
C	2	10	10	30
C	3	20	7	13
C	4	0	18	22

Would be grateful for any help!

Many thanks,

Jens

[Joe89]

Hello Phil,

and thanks for taking the time to read!

Indeed the limit values are based on the previous phase value (except for P1 where it keeps its value)

Another example:

ProjectA has the Phase and Values as shown below. Where Phase is the project progress and value for example its investment needed.

Phase 1: Value 10, as there is no previous phase it will take its own value 10 (however this phase 1 boundary is optional / not the core issue)

-> Lower Limit in PDP1 = -100% so the value is 0 for Lower Limit

-> Upper Limit in PDP1 = +100% so the value is 20 for Upper Limit

Phase 2: Value 15 of this phase. But for the limit it takes the previous phase value of 10

-> Lower Limit in PDP2 = -50% so the value is 5 for Lower Limit

-> Upper Limit in PDP2 = +50% so the value is 15 for Upper Limit

Phase 3: Value is 10 (again, this one is not following any logic it is a given quantity), but it takes the previous phase value of 15 for the limits.

-> Lower Limit in PDP3 = -30% so the value is 10.5 for Lower Limit

-> Upper Limit in PDP3 = +30% so the value is 19.5 for Upper Limit

Hope this helps to explain it.

The problem I am facing revolves mainly around taking that value for a project and its phase, changing it via a calulcation. And then putting it into the next phase as the upper / lower limit. 

I tried something of the likes of looking up the project name, an if clause to the phase with a calculation and then return this value to a variable which is then set as the value for the next phase limit. However this did not work and returned 0 for the next phase value all the time.

For some context. I am trying to create a graph based on the recorded data of cost and phase for projects to see if their increase or decrease in budget exceeded the threshold set by the phase. The further the project progresses in phases, the more mature it is and the smaller these limits for changes in budget become.

[v-zhouwen-msft · Accepted solution]

Hi @PhilipTreacy ,thanks for the quick reply, I'll add more.

Hi @Joe89 ,

The Table data is shown below:

Use the following DAX expression to create columns

Low Limit = 
VAR _phase = [Phase]
VAR _Project = [Project]
VAR _table = ALL('Table')
VAR _result = 
SWITCH(TRUE(),
_phase = 1 , [Value] * 0,
_phase = 2 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.5,
_phase = 3 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.7,
_phase = 4 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.9
)
RETURN _result

Upper Limit = 
VAR _phase = [Phase]
VAR _Project = [Project]
VAR _table = ALL('Table')
VAR _result = 
SWITCH(TRUE(),
_phase = 1 , [Value] * 2,
_phase = 2 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.5,
_phase = 3 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.3,
_phase = 4 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.1
)
RETURN _result

Final output

Best Regards,
Wenbin Zhou

[PhilipTreacy]

Hi @Joe89 

I'm not folloing the logic to create the table of values.

What is the value of Limit?  For example Phase 2 => Value P1 +/-50% Limit and in this case, what is P1?

Phil

[Joe89]

Quick answer to your question. Value P1 = Value from Phase 1