Forum Discussion

bajimmy1983's avatar
bajimmy1983
Icon for Advocate V rankAdvocate V
8 years ago
Solved

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

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. D...
Need Help
Tips and Tricks
  • bajimmy1983's avatar
    bajimmy1983
    8 years ago

     

    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! :)

Alex_SD's avatar
Alex_SD
Frequent Visitor
8 years ago

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's avatar
bajimmy1983
Icon for Advocate V rankAdvocate V
8 years ago

 

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! :)