cancel
Showing results for
Did you mean:
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:

 Account Date Limit Difference 1 07/05/2019 €        8,000 2 07/05/2019 €        5,000 3 07/05/2019 €        1,000 1 15/06/2019 €        6,000 2 15/06/2019 €        7,000 3 15/06/2019 €        3,000 1 12/07/2019 €        9,000 2 12/07/2019 €        6,000 3 12/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.

Thank you

Water

3 REPLIES 3
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:

then 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

## 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

Regular Visitor

## Re: Calculate Month on Month please

Thanks to both of you for your help and time.

I appreciate it.

Announcements