Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi - I’m having trouble calculating a request I received and was hoping someone could help. I have a table that contains total # of contacts grouped by multiple columns (such as company and role). The table has multiple snapshots of those aggregates at the beginning of every month plus current date.
I’m trying to calculate the growth of this contact count for QTD/YTD visuals with the user having the flexibility to select the months in question, but having issues where data isn’t available for a point in time as illustrated by the matrix below (using Excel so I could replace with dummy data).
Structure of table:
AS_OF_DATE | ACCOUNT_NAME | CONTACT_ROLE | NUM_CONTACTS |
4/1/2022 | Company A | Engineer | 3 |
5/1/2022 | Company A | Engineer | 17 |
5/24/2022 | Company A | Engineer | 16 |
5/1/2022 | Company A | Marketing | 1 |
5/24/2022 | Company A | Marketing | 1 |
4/1/2022 | Company A | No Role | 3 |
5/1/2022 | Company A | No Role | 2 |
5/24/2022 | Company A | No Role | 2 |
4/1/2022 | Company A | Sales | 1 |
4/1/2022 | Company A | Principal | 1 |
5/1/2022 | Company A | Principal | 1 |
5/24/2022 | Company A | Principal | 1 |
4/1/2022 | Company A | Admin | 1 |
5/1/2022 | Company A | Admin | 2 |
5/24/2022 | Company A | Admin | 2 |
5/1/2022 | Company A | Sales Manager | 1 |
5/24/2022 | Company A | Sales Manager | 1 |
5/1/2022 | Company A | Technical Manager | 1 |
5/24/2022 | Company A | Technical Manager | 1 |
Matrix results (shown as pivot table):
My expected output for Marketing role, for instance, would be +1 contact growth from 4/1 to 5/1, but in this case Power BI ignores it because that role doesn’t exist on one side of the equation.
I’m currently using this measure with no luck:
Capacity Growth =
var _min = MIN(DATE_T[Date])
var _max = MAX(DATE_T[Date])
return
CALCULATE(
SUM('CONTACT'[NUM_CONTACTS],
FILTER(ALL(DATE_T[Date]),DATE_T[Date]= _max)
)
-
CALCULATE(
SUM('CONTACT'[NUM_CONTACTS],
FILTER(ALL(DATE_T[Date]),DATE_T[Date]= _min)
)
But I’ve also went down the path of using this calculated column to get the previous amount, then comparing current vs. previous via a measure. This presents similar issues around data missing on either side of the equation.
Previous Contact Count =
CALCULATE(
MIN('CONTACT'[NUM_CONTACTS]),
FILTER('CONTACT','CONTACT'[CONTACT_ROLE_ID] = EARLIER('CONTACT'[CONTACT_ROLE_ID]) &&
'CONTACT'[AS_OF_DATE] < EARLIER('CONTACT'[AS_OF_DATE]))
)
Any ideas on how I could go about this?
Thanks!
Solved! Go to Solution.
Formula formated easier to read
Growth MTD =
VAR minperiode =
CALCULATE (
MIN ( 'Marketing growth'[AS_OF_DATE] ),
ALLEXCEPT ( 'Marketing growth', 'Marketing growth'[CONTACT_ROLE] )
)
VAR actualperiod = 'Marketing growth'[AS_OF_DATE]
VAR contactrole = 'Marketing growth'[CONTACT_ROLE]
VAR previousperiod =
CALCULATE (
MAX ( 'Marketing growth'[AS_OF_DATE] ),
ALLEXCEPT ( 'Marketing growth', 'Marketing growth'[CONTACT_ROLE] ),
'Marketing growth'[AS_OF_DATE] < actualperiod
)
VAR actualscontact =
CALCULATE (
MAX ( 'Marketing growth'[NUM_CONTACTS] ),
ALLEXCEPT ( 'Marketing growth', 'Marketing growth'[CONTACT_ROLE] ),
'Marketing growth'[AS_OF_DATE] = actualperiod
)
VAR previouscontact =
CALCULATE (
MAX ( 'Marketing growth'[NUM_CONTACTS] ),
ALLEXCEPT ( 'Marketing growth', 'Marketing growth'[CONTACT_ROLE] ),
'Marketing growth'[AS_OF_DATE] = previousperiod
)
RETURN
IF (
actualperiod = minperiode,
'Marketing growth'[NUM_CONTACTS],
actualscontact - previouscontact
)
Just to close the loop on this, I ended up pushing the logic to SQL since I couldn't figure out how to get it to calculate correctly within Power BI. I basically cross applied all missing snapshot dates for every role present, then inserted those back into the table with NUM_CONTACTS = 0. I was concerned about performance, but seems to work perfectly fine.
@Anonymousonce I accounted for the 0s, I used your DAX to finish the job and it worked perfectly. Thanks again for the help! I'm going to accept your solution as the correct one, but just to clarify for any future viewers, your table needs to have all possible dates accounted for in order for it to work.
Formula formated easier to read
Growth MTD =
VAR minperiode =
CALCULATE (
MIN ( 'Marketing growth'[AS_OF_DATE] ),
ALLEXCEPT ( 'Marketing growth', 'Marketing growth'[CONTACT_ROLE] )
)
VAR actualperiod = 'Marketing growth'[AS_OF_DATE]
VAR contactrole = 'Marketing growth'[CONTACT_ROLE]
VAR previousperiod =
CALCULATE (
MAX ( 'Marketing growth'[AS_OF_DATE] ),
ALLEXCEPT ( 'Marketing growth', 'Marketing growth'[CONTACT_ROLE] ),
'Marketing growth'[AS_OF_DATE] < actualperiod
)
VAR actualscontact =
CALCULATE (
MAX ( 'Marketing growth'[NUM_CONTACTS] ),
ALLEXCEPT ( 'Marketing growth', 'Marketing growth'[CONTACT_ROLE] ),
'Marketing growth'[AS_OF_DATE] = actualperiod
)
VAR previouscontact =
CALCULATE (
MAX ( 'Marketing growth'[NUM_CONTACTS] ),
ALLEXCEPT ( 'Marketing growth', 'Marketing growth'[CONTACT_ROLE] ),
'Marketing growth'[AS_OF_DATE] = previousperiod
)
RETURN
IF (
actualperiod = minperiode,
'Marketing growth'[NUM_CONTACTS],
actualscontact - previouscontact
)
Thanks so much, James. Really appreciate the response. This calculated column seems to give me the same results as doing a calculated column using EARLIER to get the previous value, then creating another column to subtract the two. The issue seems to still be cases where there is no value on either side of the equation.
For instance, I have two visuals below - the first is total contact count per day and the 2nd is the result of the growth column you posted. Everything seems to align except for a case like the Sales role which started at count of 1 then no longer appeared in the dataset after that. What I would expect to see is growth of -1 on the 1st of May for that role, but it shows as blank. Any idea how to get around that? It feels almost like I need to CROSS APPLY all possible values on both sides of the equation to get it to represent correctly, but if I do that in SQL, the table size will increase massively. I would imagine there would be a way to do that in DAX and be less costly. Thanks!
Hi
I am figthing with missing rows. I do not succeed to retrun a zero to have the substraction done. If anyone can help
Thanks for trying, James. If anyone has any other advice, I'd really appreciate it! Thanks!
Hi
I Hope this is what you need
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
125 | |
108 | |
60 | |
55 |