Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
HI
Try like this
= If(Month> 'Feb', sum( {<NOTE = {'SALES'}>}[BUDGET] ) + (sum( {<NOTE = {'SALES'}>}[BUDGET] ) *0.05), sum( {<NOTE = {'SALES'}>}[BUDGET] ))
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 |
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)