How to substract measure past values from the same measure

[Denisa_Stoica]

Hello!
I know that the title may not be the proper one, but what I actually want to do is to substract from the actual measure value, the last value before, from the same measure. Which I think it can be easy with some columns but I don't have columns, just the one with the weeks number. Here is an example from excel with what I want to do in Power BI, but I don't know how to filter the measure.
The 'B'column in my Power BI is actually a measure which calculates the values, and I want to create another measure that will give me the results from 'C'(formula of 'C' is on 'D' for a better understanding).

Please help me as I am stuck for allready a few days!

Thank you!

[FreemanZ]

hi @Denisa_Stoica ,

not sure if i fully get you, supposing you have a data table like:

Week	Sales
1	5
1	5
2	18
3	50
4	70
5	30
5	50

try to plot a table visual with week column and two measures like:

@Sales = SUM(data[Sales])

@Gap = 
VAR _sumpre =
SUMX(
    FILTER(
        ALL(data),
        data[Week]=MAX(data[Week]) - 1
    ),
    data[sales]
)
VAR _result = [@Sales] - _sumpre
RETURN _result

it worked like:

The attached pbix file also include another solution, if you week number has to be like "week1"...

[Denisa_Stoica]

HI @ValtteriN ,
Thank you for your quick response but I can't use SUM on a measure. As I said before, I have only measures. The one that for you is "Value" and is a column in your table, for me in my PBI it's a measure,not a column, and I cannot apply the "SUM" function on it. Do you have other suggestion?

Thank you!

[ValtteriN]

Hi,

I created the sum measure to simulate some measure you could have. So in the second measure called offset ypu can replace the:

Offset =

var _current = [Sum of weeks value]

var _previous =CALCULATE([Sum of weeks value],OFFSET(-1,ALL('Table (19)'),ORDERBY('Table (19)'[week])))

return

_current-_previous

With [This is a measure]

[Denisa_Stoica]

I understood now @ValtteriN, but I have this error message because of my "week" column as I have multiple inputs stored in one week and the relationship between tables is many to many; the error is "OFFSET's Relation parameter may have duplicate rows. This is not allowed." 😞 

[ValtteriN]

Hi,

Here is one way to do this:

Data:

Dax:

Sum of weeks value = SUM('Table (19)'[Value])

Offset =

var _current = [Sum of weeks value]

var _previous =CALCULATE([Sum of weeks value],OFFSET(-1,ALL('Table (19)'),ORDERBY('Table (19)'[week])))

return

_current-_previous

End result:

[Denisa_Stoica]

HI @ValtteriN ,
Thank you for your quick response but I can't use SUM on a measure. As I said before, I have only measures. The one that for you is "Value" and is a column in your table, for me in my PBI it's a measure,not a column, and I cannot apply the "SUM" function on it. Do you have other suggestion?

Thank you!