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
        )

Anonymous's avatar
Anonymous
Not applicable
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

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's avatar
    Anonymous
    Not applicable
    4 years ago

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

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