Forum Discussion

andrewb95's avatar
andrewb95
Helper II
5 years ago
Solved

Forecasting based on current month (With Total Sales)

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? 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

1 Reply

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.