Set Expression

[upaliwije]

Hi,

I have calculated Budget in my QV document with following expression

sum( {<NOTE = {'SALES'}>}[BUDGET] )

Now I want to increase this budget by 5% effective from the month of march. My current budget and revised amount is shown below

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Budget	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
Revised Budget	1000	1000	1050	1050	1050	1050	1050	1050	1050	1050	1050	1050

I want to rewrite the expression where budget should only be increased 5 % with effect from Mar onward

Pls let me how to modify the above expression

[MayilVahanan]

HI

Try like this

= If(Month> 'Feb', sum( {<NOTE = {'SALES'}>}[BUDGET] ) + (sum( {<NOTE = {'SALES'}>}[BUDGET] ) *0.05), sum( {<NOTE = {'SALES'}>}[BUDGET] ))

[upaliwije]

THANKS

When I apply your expression it does not work for some months

eg:JAN,APR,AUG,DEC

Pls refer Attachment below

MONTH	Budget	REVISED_BUDGET
JAN	4,125,000	4,331,250
FEB	4,025,000	4,025,000
MAR	4,225,000	4,436,250
APR	3,445,000	3,445,000
MAY	4,225,000	4,436,250
JUN	4,225,000	4,436,250
JUL	5,125,000	5,381,250
AUG	5,225,000	5,225,000
SEP	5,725,000	6,011,250
OCT	5,625,000	5,906,250
NOV	5,350,000	5,617,500
DEC	5,353,000	5,353,000
Total	56,673,000	56,673,000

[MayilVahanan · Accepted solution]

Hi

Try like this

LOAD *, MONTH(DATE#(MONTH, 'MMM')) AS MONTHNAME INLINE [

    MONTH, Budget, REVISED_BUDGET,

    JAN, "4,125,000", "4,331,250",

    FEB, "4,025,000", "4,025,000",

    MAR, "4,225,000", "4,436,250",

    APR, "3,445,000", "3,445,000",

    MAY, "4,225,000", "4,436,250",

    JUN, "4,225,000", "4,436,250",

    JUL, "5,125,000", "5,381,250",

    AUG, "5,225,000", "5,225,000",

    SEP, "5,725,000", "6,011,250",

    OCT, "5,625,000", "5,906,250",

    NOV, "5,350,000", "5,617,500",

    DEC, "5,353,000", "5,353,000"

];

MONTHNAME	Original	Revised
	56673000	56673000
Jan	4125000	4125000
Feb	4025000	4025000
Mar	4225000	4436250
Apr	3445000	3617250
May	4225000	4436250
Jun	4225000	4436250
Jul	5125000	5381250
Aug	5225000	5486250
Sep	5725000	6011250
Oct	5625000	5906250
Nov	5350000	5617500
Dec	5353000	5620650

Revised  = If(MONTHNAME > 2, sum( [Budget] ) + (sum( [Budget] ) *0.05), sum( [Budget] ))

original = Sum(Budget)