Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Can anyone please help? I would greatly appreciate it.
Thank you
Water
[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
Thanks to both of you for your help and time.
I appreciate it.
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |