cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Webbizo
Frequent Visitor

GoalSeek with a Dax measure

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. 

Result.png

 

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 

Result.png

 

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. 

 

1 ACCEPTED SOLUTION
DataZoe
Super User
Super User

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:

 

DataZoe_0-1634748458739.pngDataZoe_1-1634748478129.png

 

I have attached the PBIX too.

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

3 REPLIES 3
Webbizo
Frequent Visitor

Hi @DataZoe 

 

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

Thanks a lot. Great help.

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

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

DataZoe
Super User
Super User

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:

 

DataZoe_0-1634748458739.pngDataZoe_1-1634748478129.png

 

I have attached the PBIX too.

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors