cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Creating Growth Measures with Missing Data

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]))
)

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
)

6 REPLIES 6
Helper I

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.

Anonymous
Not applicable

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
)

Helper I

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!

Anonymous
Not applicable

Hi

I am figthing with missing rows. I do not succeed to retrun a zero to have the substraction done. If anyone can help

Helper I

Thanks for trying, James. If anyone has any other advice, I'd really appreciate it! Thanks!

Anonymous
Not applicable

Hi

I Hope this is what you need

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors