Help with the dax query

[AjithPrasath]

Hi Everyone,

   I have Sprint number column, offshore billable hours(measure) column . I also have constant offshore approved sow(lets say for ex: 11516). I have to create the new measure Remaining hours for calculating as per the below logic:

1. For first sprint alone(Ex: sprint0) I have to subtract offshore approved sow- offshore billable hours for Remaining hours.

2. From next sprint I have to subtract the previous value of the remaining hours  and offshore billable hours of the current row

lets say  offshore approved sow=100. The logic will be like below:

Sprint Number	Offshore Billable hours	Remaining hours
0	10	90(100-10)
1	20	70(90-20)
2	30	40(70-30)

[AjithPrasath]

Hi @FreemanZ ,

  I don't want to subtract from the 100 for every sprint. I just need to subtract from constant only in the first sprint. After that I have to subtract from the remaining hours value which I got in the previous sprint.

For ex: sprint 0==> 100-20=80.

           sprint 1==>80-10=70

here 20 and 10 are offshore billable hours for the respective sprints. Please find the SS below of my data:

I have to calculate the remaining hours from the above data as per the mentioned logic. Thanks in advance.

[FreemanZ]

hi @AjithPrasath 

try like:

Remaining hours =
100 - 
SUMX(
    FILTER(
        Data,
        Data[Sprint Number]<=MAX(data[Sprint Number])
    ),
    [offshore billable hours]
)

in case of issue, please consider post some sample data.