Forecasting Table

[#1]

Maybe i'm making this too complicated...

I have a simple sales dashboard...

I want to create a straight table chart that displays...

Month|Year (FUTURE)	Revenue
Next month year (11/1/2014)	3 year average for November...
12/1/2014	3 year average for December
1/1/2015	3 year average for January...

I have a "MasterDate_MonthYear" field that is built for this....just struggling on how to set this up...

[Gysbert_Wassenaar]

You will likely have to add a small table in the script that links each month to the months of the 3 years you want to use for the average. Perhaps something like:

ReportMonthYear, [Month|Year]

11/1/2014,     11/1/2011

11/1/2014,     11/1/2012

11/1/2014,     11/1/2013

12/1/2014,     12/1/2011

12/1/2014,     12/1/2012

12/1/2014,     12/1/2013

...etc

Then use ReportMonthYear as dimension in your straight table.

[jagan · Accepted solution]

Hi David,

Try like this

Dimension: None

Expressions:

Next Month Year: Caption Expression = MonthStart(Today(), 1)

=Sum({<DateDimensionName = {'>=$(=MonthStart(Today(), -11))<=$(=MonthEnd(Today(), -11))'} +

{'>=$(=MonthStart(Today(), -23))<=$(=MonthEnd(Today(), -23))'} +

{'>=$(=MonthStart(Today(), -34))<=$(=MonthEnd(Today(), -34))'} >} MeasureName)

December:Caption Expression = MonthStart(Today(), 2)

=Sum({<DateDimensionName = {'>=$(=MonthStart(Today(), -10))<=$(=MonthEnd(Today(), -10))'} +

{'>=$(=MonthStart(Today(), -22))<=$(=MonthEnd(Today(), -22))'} +

{'>=$(=MonthStart(Today(), -33))<=$(=MonthEnd(Today(), -33))'} >} MeasureName)

January:Caption Expression = MonthStart(Today(), 3)

=Sum({<DateDimensionName = {'>=$(=MonthStart(Today(), -9))<=$(=MonthEnd(Today(), -9))'} +

{'>=$(=MonthStart(Today(), -21))<=$(=MonthEnd(Today(), -21))'} +

{'>=$(=MonthStart(Today(), -32))<=$(=MonthEnd(Today(), -32))'} >} MeasureName)

Regards,

Jagan.

[#4]

I was also able to to use an aggr function on the Month of the future Month/Year

Example:

aggr(sum(Sales),month) using the month of the future month year