CODEWITHSUNDEEP
powerbidaxpowerbi-desktoppowerbi-datasource
Kiran Patel
Kiran Patel

Reputation: 65

Subtracting values from the same column but different rows in power bi

I'm calculating the difference of "closed column". All data is in one column and I'm calculating the difference between Row2-Row1 for all the rows. I'm getting results as some positive values and some negative. Positive values are coming correct but negative values are incorrect. I'm applying the formula

diff = 
Table3[Value] - 
CALCULATE(
    SUM (Table3[Value]), 
    FILTER(
        Table3, 
        Table3[Index] = EARLIER(Table3[Index])- 1
    )
).

Screenshot of my formula

Output after applying formula, -ve and +ve values

Please help how can I correct my -ve values?

Month        Week           Month End     Closed    Open   GT     IN    
01/2020      W01-2020       N             71        178    249    71
01/2020      W02-2020       N             284       189    473    213   
01/2020      W03-2020       N             550       210    760    266   
01/2020      W04-2020       N             861       185    1046   311   
01/2020      W05-2020       Y             1185      205    1390   324   
02/2020      W06-2020       N             370       206    576    370   
02/2020      W07-2020       N             665       209    874    295

Upvotes: 2

Views: 8301

Answers (2)

Aldert
Aldert

Reputation: 4313

In Dax you can use the following formulas.

In step one we create a column to get your Week Column in an order:

YearWeek = CONVERT(RIGHT(Sheet1[Week], 4) & MID(Sheet1[Week],2,2),INTEGER)

This is creating an integer value our of your year and month. Next we can use this to get the previous closed amount to be substracted where we filter first on the correct month. Be aware that I take the assumption this is a date column.

In = 
var curMonth = Sheet1[Month]
var curYW = Sheet1[YearWeek]
var filterMonthYW = FILTER(Sheet1, curMonth = Sheet1[Month] && curYW > Sheet1[YearWeek])
var MaxYW = CALCULATE(MAX(Sheet1[YearWeek]), filterMonthYW)
return Sheet1[Closed] - CALCULATE(MAX(Sheet1[Closed]), FILTER(filterMonthYW, MaxYW = Sheet1[YearWeek] ))

Sheet1 is your table..

End result: enter image description here

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

In Power Query Editor, I have added an Index column started from 1 to the data and the output is as below-

enter image description here

Now, create this below measure to get previous rows Closed value in the current row-

prev_row_closed_value = 
CALCULATE(
    SUM (your_table_name[Closed]), 
    FILTER(
        ALL(your_table_name), 
        your_table_name[Index] = MIN(your_table_name[Index]) - 1
    )
)

For calculating difference, use this below measure-

diff = 

MIN(your_table_name[Closed]) - 
CALCULATE(
    SUM (your_table_name[Closed]), 
    FILTER(
        ALL(your_table_name), 
        your_table_name[Index] = MIN(your_table_name[Index]) - 1
    )
)

Here is output from the above measure-

enter image description here

Upvotes: 1

Related Questions