GoalSeek with a Dax measure

[Webbizo]

Hello everyone,

I'm currently facing the following problem. I have a sales report (in Excel) where I have the year-to-date sales + forecast until the end of the year. 

Using Goal Seek I can quickly calculate how much do a certain salesman needs to make to achieve the certain target (Avg/month).

In the case, Salesman1 would have to sell in Oct, Nov and Dec --> Last month Sales + 593,5USD 

Does anyone have an idea how can I achieve this in power BI ? The target needs to be dynamic depending on the salesperson selected. 

Any help would be much appreciated. 

[Webbizo]

Hi @DataZoe 

Perfection! I still need to understand the logic but this is exactly what I was looking for.

Thanks a lot. Great help.

[DataZoe]

You're welcome! Let me know if you have questions on the logic to find the the number or the DAX used.

[DataZoe · Accepted solution]

Hi @Webbizo ,

Please try these measures:

Additional Needed = 
VAR _neededavg =
    CALCULATE ( [Monthly Goal Value], REMOVEFILTERS ( 'Date' ) )
VAR _neededsum = _neededavg * 12
VAR _actualsum =
    CALCULATE ( [Amount], REMOVEFILTERS ( 'Date' ) )
VAR _nummonthsremaining =
    CALCULATE (
        12 - LASTNONBLANK ( 'Date'[Month], [Amount] ),
        REMOVEFILTERS ( 'Date' )
    )
VAR _lastmonthvalue =
    CALCULATE (
        LASTNONBLANKVALUE ( 'Date'[Month], [Amount] ),
        REMOVEFILTERS ( 'Date' )
    )
VAR _try =
    DIVIDE ( _neededsum - _actualsum, _nummonthsremaining )
RETURN
    DIVIDE ( _try - _lastmonthvalue, ( ( _nummonthsremaining + 1 ) / 2 ) )

Simulation = 
VAR _lastmonthvalue =
    CALCULATE (
        LASTNONBLANKVALUE ( 'Date'[Month], [Amount] ),
        REMOVEFILTERS ( 'Date' )
    )
VAR _thismonth =
    SELECTEDVALUE ( 'Date'[Month] )
VAR _lastmonth =
    CALCULATE ( LASTNONBLANK ( 'Date'[Month], [Amount] ), REMOVEFILTERS ( 'Date' ) )
VAR _diff = _thismonth - _lastmonth
VAR _addl = [Additional Needed] * _diff
VAR _forecast =
    IF ( ISBLANK ( [Amount] ), _lastmonthvalue + _addl, [Amount] )
RETURN
    _forecast

with a Date table to extend your dates:

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2022, 1, 1 ) - 1 ),
    "Month", MONTH ( [Date] ),
    "Monthly", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
)

To get a goal seek that you can adjust and use with multiple sales persons:

I have attached the PBIX too.