Month to Month change. No Percentage. No Sum

[andyor]

I have a table with 2 columns - Deposits in INT Format, TransactionMonth in DATEformat

I need to calculate the difference (plus or minus) between each month, in INT format.

I have searched all the previous posts about month to month change, and they all are more complicated than what I'm trying to do (percentages, sums etc) and I can't seem to reverse engineer the DAX code in those into what I'm looking for

[andyor · Accepted solution]

@NickA01  that's right, just like that

[NickA01 · Accepted solution]

@andyor 
Step 1 - in query editor , add index column.

Step 2: Close & Apply
Step 3: Add new column  -this gives a column with the deposit for previous row. 

PreRow =

var previdx ='Table'[Index]-1

var currentidx ='Table'[Index]

var prevDep= CALCULATE(Sum('Table'[Deposit]),

                Filter('Table','Table'[Index]=previdx)

)

return prevDep

Step 4 add another nwe column 

Change = IF(Isblank('Table'[PreRow]),0,('Table'[PreRow]-'Table'[Deposit])*-1)

This should give you 

A better option would be to have the months as dates and also have a calendar table then use the previous period dax. 

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

[andyor]

@NickA01 I do have what you said, a calender table linked to the page and a filter that searches last 12 month, last 24 months etc. What is that more elegant solution of using pervious period?

Also question on why we're using Calculate and SUM, when there is only 1 row for each month? i.e. it's already summed?

[NickA01]

In the initial example, ther was just the Mth column to work with hence we could not use any of the wonderful time intelligence functionality.  If you have a date table, Previous Period should work. 

Also , there is a newish DAXfunction called OFFSET. - Icannot get it towork in Aug 2022 Pbi desktop and can't find a lot of info but I did find this blog. 
How OFFSET in DAX will make your life easier – Data – Marc (data-marc.com)

Regarding Calculate - Needed so we can evaluate using the filter. 

[NickA01]

@andyor 
Trying to visualise your issue. 
You want something like this ?

[andyor · Accepted solution]

@NickA01  that's right, just like that