Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |