MoM Comparison - Values / Clients do not exist in Previous Month

[bajimmy1983]

Hi community, how are you? 

I tried to discover a trick or some new function I do not know to use in order to treat this scenario. Please see attached screen.

It is a simple MoM comparison. Division is correct when I have Clients participating in both Months (previous and actual), but when I have new Clients in Actual Month and do not have it in previous one, I want to return in Evolution column 100% increase (1), but I simply don't know to perform this. 

At this moment I just can think of Nested IF functions to resolve, but I cannot arrange this part inside DAX measure. 

Scenario Picture 1:

Wrong

Scenario Picture 2:

Wrong

Actual measure blue parts I am assuming are correct. I doubt on red part (the one I want to force to be 100% of increase😞

% Billing Pilot 2017 Variation BRL = 
IF (
    [Billing Pilot 2017 All Bases Last Month according to Month Selection] < 15
        || [Billing Pilot 2017 All Bases Selected Month] < 15;
    BLANK ();
    IF (
        ISBLANK ( [Billing Pilot 2017 All Bases Last Month according to Month Selection] )
            && ISBLANK ( [Billing Pilot 2017 All Bases Selected Month] );
        BLANK ();
        IF (
            ISBLANK ( [Billing Pilot 2017 All Bases Last Month according to Month Selection] )
                && [Billing Pilot 2017 All Bases Selected Month] <> 0;
            1;
            IF (
                [Billing Pilot 2017 All Bases Last Month according to Month Selection]
                    = BLANK ()
                    && [Billing Pilot 2017 All Bases Selected Month] > 0;
                1;
                DIVIDE (
                    [Billing Pilot 2017 All Bases Selected Month];
                    [Billing Pilot 2017 All Bases Last Month according to Month Selection];
                    0
                )
                    - 1
            )
        )
    )
)

[Alex_SD]

Hi,

Try breaking it down into sections

1) Get the previous months value for that client

Previous Month = CALCULATE (
        SUM ( [Previous Value] ),
        FILTER ( ALL( 'Calendar' ), 'Calendar'[Month] = MAX ( 'Calendar'[Month] ) -1 )
)

2) Now calculate from Actual

Actual Month = IF (ISBLANK( [Previous Month] ), 100%, 
DIVIDE(x,y)
)

Hope that makes sense

[bajimmy1983 · Accepted solution]

Hi @Alex_SD, thank you so much for your reply and sorry to reply you just now. 

I tried as sugested, but did not work! No problem because after some tests I did by myself I could see the gap and very happy I could found the solution with two possible DAX measures. Why two? One is using nested IFs (not the best way I think and I am not talking here about any performance increasing) and the other using a more implemented SWITCH function. Please see both measures below. 

Nested IFs

% Billing Pilot 2017 Variation BRL = 
IF (
    ISBLANK ( [Billing Pilot 2017 All Bases Last Month according to Month Selection] )
        && ISBLANK ( [Billing Pilot 2017 All Bases Selected Month] ),
    BLANK (),
    IF (
        AND (
            [Billing Pilot 2017 All Bases Last Month according to Month Selection] = 0,
            [Billing Pilot 2017 All Bases Selected Month] = 0
        ),
        0,
        IF (
            NOT ( [Billing Pilot 2017 All Bases Last Month according to Month Selection] )
                && [Billing Pilot 2017 All Bases Selected Month] <> 0,
            1,
            DIVIDE (
                [Billing Pilot 2017 All Bases Selected Month],
                [Billing Pilot 2017 All Bases Last Month according to Month Selection],
                0
            )
                - 1
        )
    )
)

SWITCH:

% Billing Pilot 2017 Variation BRL = 
SWITCH (
    TRUE (),
    AND (
        ISBLANK ( [Billing Pilot 2017 All Bases Last Month according to Month Selection] ),
        ISBLANK ( [Billing Pilot 2017 All Bases Selected Month] )
    ), BLANK (),
    AND (
        [Billing Pilot 2017 All Bases Last Month according to Month Selection] = 0,
        [Billing Pilot 2017 All Bases Selected Month] = 0
    ), 0,
    AND (
        NOT ( [Billing Pilot 2017 All Bases Last Month according to Month Selection] ),
        [Billing Pilot 2017 All Bases Selected Month] <> 0
    ), 1,
    DIVIDE (
        [Billing Pilot 2017 All Bases Selected Month],
        [Billing Pilot 2017 All Bases Last Month according to Month Selection],
        0
    ) -1
)

Thank you again community for your always great help and support. 

Have a nice week! 🙂

[Anonymous]

Hi @bajimmy1983,

Can you please provide some sample data to test? Without any data, it is hard to analysis and modify your formula.

Regards,

Xiaoxin Sheng

[bajimmy1983]

Hi @Anonymous thanks for your help. 

Yes, please see Link to Sample data

Thanks a lot again and best regards,