# Projected Goal

• ### Question

• I have the sales goals and what i catched up month to month. I want to create a column that returns me the projection for the current month. How can i calculate this?
• Edited by Monday, May 29, 2017 6:59 PM
Monday, May 29, 2017 6:58 PM

• Hello,

So, one way to look at this is to build the model that would consist of 3 tables:

• Transactions - fact table that captures sales of our products throughout the year
• Sales Goal Table - separate table with annual sales goals for our products
• Date Dimension - standard dimdate we use in dimensional modeling.

Here is the source sample

The logic would be to create a year to date sales measure from ongoing product transactions and divide it by annual sales goals in order to get that ratio of sales goals met.

```% Sales Goal :=
IF (
[Quantity Sold] > 0,
DIVIDE (
CALCULATE (
[Quantity Sold],
FILTER (
ALL ( DimDate ),
DATE ( YEAR ( DimDate[Date] ), 1, 1 )
= DATE ( YEAR ( MAX ( DimDate[Date] ) ), 1, 1 )
&& DimDate[Date] <= MAX ( DimDate[Date] )
)
),
SUM ( SalesGoal[Annual Sales Goal] ),
0
),
0
)```

Here is our result

*** zero shows months without sales

Tuesday, May 30, 2017 8:37 PM

### All replies

• Hi Jac1994,

To solve your issue more efficiently, would you please share sample data and the expected results ?

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Tuesday, May 30, 2017 3:17 AM
• Hello,

So, one way to look at this is to build the model that would consist of 3 tables:

• Transactions - fact table that captures sales of our products throughout the year
• Sales Goal Table - separate table with annual sales goals for our products
• Date Dimension - standard dimdate we use in dimensional modeling.

Here is the source sample

The logic would be to create a year to date sales measure from ongoing product transactions and divide it by annual sales goals in order to get that ratio of sales goals met.

```% Sales Goal :=
IF (
[Quantity Sold] > 0,
DIVIDE (
CALCULATE (
[Quantity Sold],
FILTER (
ALL ( DimDate ),
DATE ( YEAR ( DimDate[Date] ), 1, 1 )
= DATE ( YEAR ( MAX ( DimDate[Date] ) ), 1, 1 )
&& DimDate[Date] <= MAX ( DimDate[Date] )
)
),
SUM ( SalesGoal[Annual Sales Goal] ),
0
),
0
)```

Here is our result

*** zero shows months without sales

Tuesday, May 30, 2017 8:37 PM