Please help!! I am sure it supposed to be simple...but it is taking me ages to try and figure this out!
I am trying to create a report whereby I show the cost per piece over the last 12 months - my report is dynamic so the user can change the current month and thus the preceding 11 months before.
The problem I am encountering is that I am trying to show a variance column based on the "current month" and the Dec of the previous year.
I have taken the following steps:
1) I have created the formula where [Date Control] is my parameter to select the month.
*Current Month Cost Per Unit*: IF ([Date]) = [Date Control] THEN [Cost Per Unit] ELSE 0 END
*Last Month Last Year (LM LY) Cost Per Unit*: IF ([Date]) = [LM LY] THEN [Cost Per Unit] ELSE 0 END
* Variance Calc*: [LM LY Cost Per Unit] - [Current Month Cost Per Unit]
2) I have added the variance calc to my rows as a discrete value - The problem here is that rather than each part sitting on one line, when I add this calculation, it sits on three lines - one line has the "current month" cost, one the Dec'15 cost and the other has the remaining months. I then get a variance for both the Dec'15 row and the current month row - summed up, the variance would be correct but the current way it is displayed is not helpful:
(This is what it looked like before I added the variance calc):
3) I thought I had a solution - to duplicate the report and create a formula firstly to only show the previous dec and the current month. This would then enable me to create the below formula:
SUM([Cost Per Unit]) - LOOKUP(ZN(SUM([Cost Per Unit])), -1)
However, although the calculation is then correct (i.e. it actually subtracts the values between Dec15 and Nov16, it still shows on two lines and shows a "Null":
The report I am trying to produce is very long, with many part costs, and so the idea of a second worksheet may prove difficult when lining it up in the Dashboard...I have already noticed that the order doesn't always stay the same etc, and I also wouldn't know how to hide the Dec month in the second worksheet (as it will already be one of the months showing in the first worksheet).
So my queries are as follows:
1) How would you go about trying to present this long report with variance column for current month versus previous Dec (I will need to present the months in chronological order, so the previous Dec will never be in the same position compared to the current month)?
2) How can I display just one row for each part so that comparison is easy and the variance calculates properly?
2) How can I get the variance column to sit as the last column in the report?
3) How can I apply a different colour only to the variance column?
I'd be so grateful for any help you can offer, this is starting to give me a headache!