Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How To Calculate dynamic projections

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

General Comment
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

     

     

     

2 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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.