Creating Growth Measures with Missing Data

[hrafnkel11]

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!

[hrafnkel11]

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 · Accepted 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
    )

[hrafnkel11]

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]

Hi

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

[hrafnkel11]

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

[Anonymous]

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)