Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
My first post here ! Apologies if I posted in the wrong place
I have 2 straight tables to show MTD and YTD income statement.
The set analysis expression for MTD is : Sum ({<MonthName={$(=ONLY(MonthName))},[MeasureName]={'NPS','NNS','Marginal Contribution','Product Contribution','Operating Profit OP1'}>}[2014 Actual])/1000
The set analysis expression for YTD is : Sum ({<MonthID={">=1<=$(=(max(MonthID)))"},[MeasureName]={'NPS','NNS','Marginal Contribution','Product Contribution','Operating Profit OP1'}>}[2014 Actual])/1000
MonthName is Jan, Feb, Mar, etc (user can select via a ListBox
MonthID is 1,2,3,etc
These are both created via Load Inline
The issue is both MTD and YTD numbers are the same. Can anyone help.
Thanks.
Try this expression for YTD:
Sum ({<MonthName=, MonthID={">=1<=$(=(max(MonthID)))"},[MeasureName]={'NPS','NNS','Marginal Contribution','Product Contribution','Operating Profit OP1'}>}[2014 Actual])/1000
What is does is disregard the selection in MonthName, so that MonthID can be set to include other months too so >=1 actually does something,
Try this expression for YTD:
Sum ({<MonthName=, MonthID={">=1<=$(=(max(MonthID)))"},[MeasureName]={'NPS','NNS','Marginal Contribution','Product Contribution','Operating Profit OP1'}>}[2014 Actual])/1000
What is does is disregard the selection in MonthName, so that MonthID can be set to include other months too so >=1 actually does something,
Thanks so much for that! It worked!!
I have another question. Below is the result. The MeasureName is a dimension and the 2014 Actual/2014 Plan/ 2013 actuals are the fact table. How do I get a row at the bottom to calculate 'Profit %' which is the 'NPS' divided by 'Operating Profit' ?
MeasureName | 2014 Actual | 2014 Plan | Var : Act vs Plan | Var % | 2013 Actual |
NPS | $68,259 | $72,199 | -$3,940 | 0.94228581 | $58,617 |
NNS | $58,871 | $63,173 | -$4,302 | 0.92692407 | $50,238 |
Marginal Contribution | $25,812 | $28,855 | -$3,043 | 0.88210098 | $22,236 |
Product Contribution | $16,866 | $17,651 | -$785 | 0.95344995 | $12,835 |
Operating Profit OP1 | $9,446 | $10,285 | -$839 | 0.91118349 | $5,682 |
Thanks.
That's somewhat tricky. Perhaps this blog post can help: How IntervalMatch Solved My Profit and Loss Dilemma