Subtract previous row from current row and store results in another table

[Anonymous]

I am trying to subtract the previous row from the current row and save the results in another table.

As done in the image, I want to subtract the values on the previous date from the ones from the current date. I want to save it in another table.

Kindly help.

[Cmcmahan]

I don't have an answer for you, just something to think about:

What happens when you re-order the data? Sort by Variable 1 instead of date? Would you want the whole thing re-calculated?

[Cmcmahan]

Then you could to do something like 

VAR1 = SUBTRACT("Table 1"[Var 1],CALCULATE(SELECTEDVALUE("Table 1"[Var 1]),FILTER("Table1", [Date]=PREVIOUSDAY(EARLIER([Date]))))

Or if the data isn't guaranteed to be day by day and some time skips use this instead of the above FILTER:

LASTDATE(FILTER("Table1", [Date]<EARLIER([Date])))

What you're doing here is taking the every Var 1, then getting the previous date and using that Var 1 in the SUBTRACT statement. EARLIER here isn't talking about time, but rather context, so it returns the current value of Var1.

Note that I haven't tested this, so you'll have to play a little bit with the syntax to try and make it work.

[Anonymous]

That DAX code makes sense logically.

But it doesn't recognise the function SUBTRACT().

Is there any other way to do it?

[Cmcmahan]

Just use a minus sign instead of a function.  It should work fine.

[Anonymous]

I am not able to figure out the correct syntax for that.

[Cmcmahan]

VAR1 = (SELECTEDVALUE("Table 1"[Var 1]) - CALCULATE(SELECTEDVALUE("Table 1"[Var 1]),FILTER(ALL("Table1"), [Date]=PREVIOUSDAY(EARLIER([Date]))))

[Anonymous]

It gives me a syntax error saying that:

"The first argument to 'PREVIOUSDAY' must specify a column."

If I remove the EARLIER keyword and simply write Table1[Date] = PREVIOUSDAY( Table1[Date]), It doesn't give any syntax error, but the columns are just blank.

[Cmcmahan]

Huh. PREVIOUSDATE works oddly. It takes a column of dates, not a single date. My mistake. Try VALUES instead of EARLIER in order to get the dates as a column of dates instead of a single date.

VAR1 = (SELECTEDVALUE(Table 1[Var 1]) - CALCULATE(SELECTEDVALUE(Table 1[Var 1]),FILTER(ALL(Table1), [Date]=PREVIOUSDAY(VALUES(Table1[Date]))))

Here's the table I was able to create with a small set of sample data:

[Anonymous]

In that case it gives a blank column. 

Could there be any issue with the other table?

[Cmcmahan]

Wait, are you using a calculated column, or a measure?  I set up my previous solution as a measure.

[Anonymous]

We're trying to create a calculated column.

It has to become a part of the table (data source) that we are using to create visuals. 

[Cmcmahan · Accepted solution]

OK, for whatever reason, in my tests I can't get a calculated column expression to return anything but blanks. 

However, I was able to create the measure above, calling it DiffPreviousDayMeasure, and then use this to create a column of correct values:

DiffColumn = [DiffPreviousDayMeasure]

I'll keep trying for a bit to see if I can get it to work without the intermediate measure, but I make no promises.

EDIT: OK, I figured out where I had the disconnect. I stumbled upon this, which gets close, in that it uses previous values to calculate the current value. During the creation of calculated columns, you can't use CALCULATE and a handful of other DAX expressions.  It's already going row by row, so any reference to Table[Value] evaluates to the current value.  In order to get the previous value, you need to create a var that uses a mix of MAXX to determine the biggest date prior to an EARLIER term.   This seems like a lot of effort to get the syntax right, so I'm going to stick with my previous answer.  Create this as a measure, and then set the calculated column equal to the measure.

[Anonymous]

That works!

Thank you very much!

[Anonymous]

I want to find the difference between 2 consecutive dates.

So if the variable 1 is re-arranged, I would still like to see the difference between the values on 2 consecutive dates.