Forum Discussion
MoM Comparison - Values / Clients do not exist in Previous Month
- 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! :)
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
Hi Anonymous thanks for your help.
Yes, please see Link to Sample data
Thanks a lot again and best regards,