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
Anonymous
Not applicable

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
Anonymous
Not applicable

[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

Anonymous
Not applicable

Thanks to both of you for your help and time.

 

I appreciate it.

Anonymous
Not applicable

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. 

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.

Top Solution Authors