Calculating Date Difference on condition

[meghansh]

Hello,
I have this table.

ID   Phase     Amount  PhaseSequence  HasPhase2

1       Red      500          1                        No

1       Green   750         3                        No

2       Red       800         1                        Yes

2       Yellow   900         2                        Yes 

2       Green   1500        3                        Yes

I want to create another column with condition as:

If hasphase2 = "Yes"

then calculate Amount of Phase 2 - Amount of Phase 1 for ID = 2
and Amount of Phase 3 - Amount of Phase 2

else Amount of Phase 3 - Amount of Phase 1

Result shoult be like

ID   Phase  Amount  Difference

1       1        500         

1       3        750         250

2       1        800         

2       2        900         100  

2       3        1500        600

Thanks

[quantumudit · Accepted solution]

Hello @meghansh 
To achieve the desired result, you can utilize the following DAX formula:

Difference = 
VAR _id = PhaseTbl[ID]
VAR _phaseNum = PhaseTbl[PhaseSequence]
VAR _conditionalPhaseNum = IF(PhaseTbl[HasPhase2] = "No", 1, _phaseNum-1)
VAR _deductionAmount = 
CALCULATE(
    SUM(PhaseTbl[Amount]),
    FILTER(
        ALL(PhaseTbl),
        PhaseTbl[ID] = _id && PhaseTbl[PhaseSequence] = _conditionalPhaseNum
))

RETURN
IF(PhaseTbl[PhaseSequence] <> 1, PhaseTbl[Amount] -_deductionAmount)

Here is the screenshot of the solution for your reference:

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[Anonymous · Accepted solution]

Hi @meghansh 

It seems that you want to calculate the difference row by row for different IDs. Besides the method provided by quantumudit , you can also consider the following method.

First create an index column in power query:

Then you can try the following measure:

Measure = 
var _ID = SELECTEDVALUE('Table'[ID])
VAR _index = SELECTEDVALUE('Table'[Index])
var _current = SELECTEDVALUE('Table'[Amount])
VAR _previous = CALCULATE(SELECTEDVALUE('Table'[Amount]),FILTER(ALL('Table'),'Table'[Index] = _index -1 && 'Table'[ID] = _ID))

RETURN
IF(_previous <> BLANK(),_current - _previous,BLANK())

Select "show items with no data"

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[quantumudit · Accepted solution]

Hello @meghansh 
To achieve the desired result, you can utilize the following DAX formula:

Difference = 
VAR _id = PhaseTbl[ID]
VAR _phaseNum = PhaseTbl[PhaseSequence]
VAR _conditionalPhaseNum = IF(PhaseTbl[HasPhase2] = "No", 1, _phaseNum-1)
VAR _deductionAmount = 
CALCULATE(
    SUM(PhaseTbl[Amount]),
    FILTER(
        ALL(PhaseTbl),
        PhaseTbl[ID] = _id && PhaseTbl[PhaseSequence] = _conditionalPhaseNum
))

RETURN
IF(PhaseTbl[PhaseSequence] <> 1, PhaseTbl[Amount] -_deductionAmount)

Here is the screenshot of the solution for your reference:

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree