How To Calculate dynamic projections

[Anonymous]

HI All

i am trying to calculate dynamic projection for sales 

i have a defined start date as 01/05/2021 and a defined end date as 01/05/2023

i have a target of 375 sales across this period

375 / 25 months = 15 sales per month

what i am trying to do is produce a measure that shows me monthly required sales if there are shortfalls in actual monthly sales

if i only make 5 sales in the first month

monthly target sales 15 - actual sales 5 = shortfall in sales 10

new monthly projection for ther period of time left

(375 - 5) / 24 = new monthly projection = 15.4 sales per month

i want to be able to calculate this so i can then plot a curve of actual monthly sales / projected monthly sales/ dynamic projected sales required to meet targets

any / all help would be appreciated 😊

thank you all for looking

[v-kkf-msft · Accepted solution]

Hi @Anonymous ，

Please try the following formula:

Measure = 
var target = 375
var Months = 25
var SumAct = SUM ( 'Table'[actual sales] )
var DiffMonth = 
    DATEDIFF ( 
        CALCULATE ( 
            MIN ( 'Date'[Date] ),
            FILTER ( ALL ( 'Date' ), SumAct = BLANK () )
        ),
        MAXX ( ALL ( 'Date' ), 'Date'[Date] ),
        MONTH
    )
var AllAct = 
    CALCULATE (
        SUM ( 'Table'[actual sales] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
return 
    IF (
        SumAct <> BLANK (), 
        target / Months - SumAct, 
        ( target - AllAct ) / DiffMonth
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.