Forum Discussion

hrafnkel11's avatar
hrafnkel11
Helper I
4 years ago
Solved

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). Th...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
hrafnkel11
Helper I
4 years ago

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.