Subtracting a cell value from a another cell (measure) in a single column

[aktripathi2506]

HI,

I have the following scenario:

In the visual I am summarising the weekly data.

So I have week then next column I have is hours, which is basically the sum of hours for the week.

I have a measure which has certain value.

Now I want the third column having ongoing subtraction:

If the value of measure is 3200 then value of column 'new' should be as follows:

Week	Hours	New
1	25	3175
2	48	3127
3	386	2741
4	374	2367
5	688	1679
6	352	1327
7	534	793
8	365	428
9	164	264
10	295	-31

Thanks & Regards,

A

[jahida · Accepted solution]

New version of the formula:

Measure = 500 - MINX(test, SUMX(FILTER(ALL(test), test[Week] <= EARLIER(test[Week]) && test[Week] > 0 && test[Report Week] = EARLIER(test[Report Week])), test[Hours]))

[Vvelarde]

Can you share us the pbix file; deleting all the data that you considered critical to show. I think this will be the  easy way to help you in more reduced time.

[Vvelarde · Accepted solution]

After seeing his data; the key is that have weeks with negatives values, so the measure was modified to:

MeasureToSubstractions = [Measure]-CALCULATE(Sum(test[Hours]);FILTER(ALLEXCEPT(test;Test[Year];test[Report Week]);test2[Week]<=max(test[Week]) && test[Week]>0))

[aktripathi2506]

Thank you so much guys. Both of you @Vvelarde and @jahida for your sincere efforts and time.

Thanks a lot. It worked now, finally :))

[Vvelarde]

MeasureValues = [Measure]-CALCULATE(Sum(WeekReport[hours]);FILTER(ALLEXCEPT(WeekReport;WeekReport[Reporting Week]);WeekReport[week]<=max(WeekReport[week])))

[Vvelarde]

Hi @aktripathi2506

Values = [Measure]-CALCULATE(Sum(WeekReport[hours]);FILTER(ALLEXCEPT(WeekReport;WeekReport[Reporting Week]);WeekReport[week]<=max(WeekReport[week])))