Custom Calculated Measure

[iakut]

Hello!!!

Im new in the bi world, i need your help!

I have a conecction to an olap cube with the meausres Sales, and the dimesion Calendar.

If i put the calendar and sales in a pivot table show:

January 2015 - 1000 $
February 2015 - 2000 $
March 2015 - 3000 $
April 2015 - 4000 $
May 2015 - 5000 $
June 2015 - 6000 $
July 2015 - 7000 $
August 2015 - 8000$
Septemer 2015 - 9000$
October 2015 - 10000 $
November 2015 - 11000$
December 2015 - 120000 $
January 2016 - 1500 $
February 2016 - 2500 $
March 2016 - 3500 $
April 2016 - 4500 $
May 2016 - 5500 $
June 2016 - 6500 $
July 2016 - 7500 $
August 2016 - 8500$

i need a create a custom measure, the name is sales forecast, andin a pivot table with the future and current month is Month Last Year * 1,50, and the previous mont is Sales A very simple forecast.

If the measeure in a pivot table show this:

January 2015 - 1000 $
February 2015 - 2000 $
March 2015 - 3000 $
April 2015 - 4000 $
May 2015 - 5000 $
June 2015 - 6000 $
July 2015 - 7000 $
August 2015 - 8000$
Septemer 2015 - 9000$
October 2015 - 10000 $
November 2015 - 11000$
December 2015 - 120000 $
January 2016 - 1500 $
February 2016 - 2500 $
March 2016 - 3500 $
April 2016 - 4500 $
May 2016 - 5500 $
June 2016 - 6500 $
July 2016 - 7500 $
August 2016 - 8500$
September 2016 - 13500 $
October 2016 - 15000 $
November 2016 - 16500 $
December 2016 - 18000 $

BOLD = Forecast

Thanks a lot

 

[Ozeroth]

Hi,

Have a look at this:
Recursive Calculations in PowerPivot using DAX | Gerhard Brueckl's BI Blog

Your situation is similar but you want to look back a year so you would use SAMEPERIODLASTYEAR in your formula.

Or possibly a pattern like this might work (if you are happy to forecast no more than 12 months into the future):

Sales Forecast =
SUMX (
    VALUES ( 'Calendar'[Month] ),
    IF (
        ISBLANK ( [Sales Actual] ),
        CALCULATE ( [Sales Actual], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) * 1.15
    )
)

 

[Dan80]

Ozeroth strikes again, genius as always Owen!

 

[iakut]

Thank you !!

And good link to the blog!!

After read I think the post is not in the correct forum, because i need a MDX calculated measure, and this is DAX.



Can you help me??

Best regards

 

[iakut]

I open this topic in the correct forum

Best regards

 

[iakut]

Hello

Is possible to move this topic to the forum excel. I Opened a new pust but is closed

Thanks