Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andyor
Resolver I
Resolver I

Month to Month change. No Percentage. No Sum

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

2 ACCEPTED SOLUTIONS

@NickA01  that's right, just like that

View solution in original post

NickA01
Resolver III
Resolver III

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

NickA01_0-1663765104961.png

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 

 

NickA01_2-1663765927505.png

 

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 .



View solution in original post

5 REPLIES 5
NickA01
Resolver III
Resolver III

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

NickA01_0-1663765104961.png

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 

 

NickA01_2-1663765927505.png

 

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 .



@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?

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
Resolver III
Resolver III

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

NickA01_0-1663764029087.png

 

@NickA01  that's right, just like that

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.