# Thread: Custom Calculated Measure Thanks: 0 Likes: 0

1. ## Custom Calculated Measure

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

2. ## Re: Custom Calculated Measure

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):

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

3. ## Re: Custom Calculated Measure

Ozeroth strikes again, genius as always Owen!

4. ## Re: Custom Calculated Measure

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

5. ## Re: Custom Calculated Measure

I open this topic in the correct forum

Best regards

6. ## Re: Custom Calculated Measure

Hello

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

Thanks

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•