cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
water_hydration Regular Visitor
Regular Visitor

Calculate Month on Month please

Hi everyone! 

 

I am having trouble digging out a formula to get the month on month difference for each Account Number.

 

My data is structed as below:

 

AccountDateLimitDifference
107/05/2019 €        8,000 
207/05/2019 €        5,000 
307/05/2019 €        1,000 
115/06/2019 €        6,000 
215/06/2019 €        7,000 
315/06/2019 €        3,000 
112/07/2019 €        9,000 
212/07/2019 €        6,000 
312/07/2019 €        5,000 

 

 

My issue is how to find the difference in the limit's for each account and how it differs month to month.

 

Can anyone please help? I would greatly appreciate it.

 

Thank you

 

Water

3 REPLIES 3
Super User I
Super User I

Re: Calculate Month on Month please

First step is to create a calendar table. Can do this a few different ways but a quick (and no really the best way) is to use CALENDARAUTO().  

 

Then mark that as a date table:

How to Mark as Date Table.pngthen using the columns from that new date table as filters ( rows, columns, slicers, etc) you can then leverage the time-intelligence functions.  Take a look at this page from microsoft:

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

 

Give a try and see what you can come up with. 

Super User IV
Super User IV

Re: Calculate Month on Month please

[Difference] = -- calculated column 
var __account = T[Account]
var __date = T[Date]
var __limit = T[Limit]
var __prevDate =
	MAXX(
		FILTER (
			T,
			T[Account] = __account
			&&
			T[Date] < __date
		),
		T[Date]
	)
var __prevLimit =
	MAXX(
		FILTER(
			T,
			T[Account] = __account
			&&
			T[Date] = __prevDate
		),
		T[Limit]
	)
var __diff = __limit - __prevLimit
RETURN
	__diff

The assumption is that there should be only one entry for each account each month. If this is true, the code does what it should. You have to figure out what to do in the very first month and how you want to report the difference. Currently, it returns the current value of Limit if we're in the very first month of the account.

 

Best

Darek

water_hydration Regular Visitor
Regular Visitor

Re: Calculate Month on Month please

Thanks to both of you for your help and time.

 

I appreciate it.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors