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
hrafnkel11
Helper I
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):

hrafnkel11_0-1653494866857.png

 

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!

 

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
    )

View solution in original post

6 REPLIES 6
hrafnkel11
Helper I
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
    )

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!

 

hrafnkel11_1-1653505221815.png

hrafnkel11_2-1653505234647.png

 

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

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)
JamesFr06_0-1653499119899.png

 

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.