Forecasting based on current month (With Total Sales)

[andrewb95]

I want to make a prediction based on the current month total sales (I have a measure [TotalSalesMonth]). 

Example if it is the 5th of the month and there have been 50 sales to date then I would assume there are 10 sales per day and by the end of the month would predict 300 sales (30 day month) or 310 sales in a (31 day month) and so on... 

I have the item TotalSalesMonth as I have already suggested and imagine I will use EOMonth and Today for time intelligence. Please advise? 

[v-alq-msft · Accepted solution]

Hi, @andrewb95 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below.

TotalSalesMonth To Date = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        [Date]<=MAX('Table'[Date])&&
        FORMAT([Date],"mmm yyyy")=FORMAT(MAX('Table'[Date]),"mmm yyyy")
    )
)

Forecast TotalSalesMonth = 
var _days = DAY(EOMONTH(MAX('Table'[Date]),0))
var _avg = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Date]<=MAX('Table'[Date])&&
        FORMAT([Date],"mmm yyyy")=FORMAT(MAX('Table'[Date]),"mmm yyyy")
    )
)
return
_avg*_days

Result:

Best Regards

Allan

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