Dynamic Sales Goals per Month

[Oros]

Hello,

I have a Sales table and a Calendar table.

The total sales goal for the enitre year is set at $1,200. Then the monthly goal is a static goal of $100 per month ($1,200/12)

What would be the correct measure to make the succeeding monthly goal take into account the negative difference between the actual sales vs the sales goal for the month?  Thanks.

In other words...

Monthly Sales Goal=$100

If actual sales goal < monthly sales goal, then add the diffrence to the Monthly Sales Goal.

For example,

If January actual sales = $90, then the February Goal should be $100 +$10.

If the February actual sales is again $90, then the march goal should be $100+$10+$10

If the March actual sales is $120, then the April goal should be back to $100

Monthly Sales Goals

[lbendlin · Accepted solution]

Subtract the cumulative sales for a given month from the cumulative goal for that month.

[danextian · Accepted solution]

Hi @Oros 

Use a separate dates table, relate that to your fact table and create these measures:

Monthly Goal = 
100

Cumulative Difference =
MAX (
    0,
    SUMX (
        FILTER (
            ALL ( CalendarTable ),
            CalendarTable[Month] <= MAX ( CalendarTable[Month] )
        ),
        CALCULATE ( [Monthly Goal] - SUM ( 'Table'[Actual Sales] ) )
    )
)

Adjusted Goal = 
---even if the cumulative difference is negative, the goal is minimum of 100
IF (
    [Cumulative Difference] > 0,
    [Monthly Goal] + [Cumulative Difference],
    [Monthly Goal]
)

Please see attached sample pbix for your reference.

[Oros · Accepted solution]

Hi @danextian,

Here is the sample data.  Thanks.

https://filebin.net/z393w5ubbbngpl5x

[v-kaiyue-msft · Accepted solution]

Hi @Oros ,

You can refer to the following steps:

1. in power query editor, after selecting salesperson column, select inverse pivot other columns. Same operation for both tables.

2. create relationships between the tables.

3. create these measures.

Monthly Sales Goal = 
CALCULATE(MAX('GOALS'[Value]),FILTER('GOALS','GOALS'[SALESPERSON] = MAX('SALES'[SALESPERSON])))


Cumulative Difference = 
CALCULATE(
    SUMX(
        Sales,
        [Monthly Sales Goal] - SALES[Value]
    ),
    FILTER(
        ALL('Calendar'),
        Calendar[Date] <= MAX(Calendar[Date])
    )
)

Salesperson Cumulative Difference = 
CALCULATE(
    SUMX(
        Sales,
        IF('SALES'[Value] <> BLANK(),
        [Monthly Sales Goal] - SALES[Value],
        0)
    ),
    FILTER(
        ALL('Calendar'),
        Calendar[Date] <= MAX(Calendar[Date])
    ),
    'SALES'[Salesperson] = MAX('SALES'[Salesperson])
)

Measure = 
SUMX(FILTER(ALLSELECTED('SALES'[Attribute]),'SALES'[Attribute] <= MAX('SALES'[Attribute])),[Salesperson Cumulative Difference])

Salesperson Adjusted Monthly Goal = 
[Monthly Sales Goal] + [Measure]

The final result is shown below, more details can be found in the attachment.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Oros]

Hi @v-kaiyue-msft,

Thank you so much!  It works! 

[danextian · Accepted solution]

Hi @Oros 

Use a separate dates table, relate that to your fact table and create these measures:

Monthly Goal = 
100

Cumulative Difference =
MAX (
    0,
    SUMX (
        FILTER (
            ALL ( CalendarTable ),
            CalendarTable[Month] <= MAX ( CalendarTable[Month] )
        ),
        CALCULATE ( [Monthly Goal] - SUM ( 'Table'[Actual Sales] ) )
    )
)

Adjusted Goal = 
---even if the cumulative difference is negative, the goal is minimum of 100
IF (
    [Cumulative Difference] > 0,
    [Monthly Goal] + [Cumulative Difference],
    [Monthly Goal]
)

Please see attached sample pbix for your reference.

[Oros]

Hi @danextian ,

Thank you so much for your quick response and for sending a sample pbix.

It seems that your solution is pretty straightforward.  What if the monthly goal is dependent on each salesperson.  In other words, the monthly goal of $100 can change depending of the selected salesperson?  How do you adjust the the measures from your solution?  Thanks again.

[danextian]

Please provide a sample data so the solution can be based on that. And please, not an image.

[Oros]

Hi @danextian,

Unfortunately, I do not see any method for me to attach a pbix file.

[danextian]

You can post a link to cloud.

[Oros · Accepted solution]

Hi @danextian,

Here is the sample data.  Thanks.

https://filebin.net/z393w5ubbbngpl5x

[Oros]

Hi @danextian,

It works too!  Thanks. 

[danextian]

Please mark my post as solution if it does. Thanks!

[lbendlin]

Next step is to add a proper calendar table to your data model. That will help you to sort the months in the right order, and will allow you to caclulate this across (fiscal) years.

[lbendlin · Accepted solution]

Subtract the cumulative sales for a given month from the cumulative goal for that month.

[Oros]

Hi @lbendlin,

It works.  Thanks.

[Oros]

Hi @lbendlin ,

Thank you very much for your quick reply.

What would be the exact measure to represent this solution?  Thanks again.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Oros]

Hi @lbendlin,

Here is the sample data.

https://filebin.net/z393w5ubbbngpl5x