How to use regression to estimate trending data until end of month based on current data?

[jaggy123]

Hi,

I have a usecase that I'm trying to build out that I could use help with. Let's say that I have a sales table which is very simple, like:

Sale Date	Sale Amount
2023-01-01	$50
2023-01-02	$65
2023-01-03	$85
2023-01-04	$20

I want to be able to use regression or some other way to estimate the total sales amount we could expect to see by the end of the month. The idea is that this calculation should take the real sales amount prior to the maximum date that we have data up to (Jan 4th in this case), and then add that real value to an estimated value for the rest of the month - resulting in a predicted total sales amount per month based on the data we currently have so far.

I've got the regression part working, but it's just the combining of the two in a single calculated measure that is not working. Anyone have any idea how this could work?

[Sahir_Maharaj · Accepted solution]

Hello @jaggy123,

1. Determine the real sales amount prior to the maximum date available in your data:

RealSalesAmount = 
CALCULATE(
    SUM('Sales'[Sale Amount]),
    'Sales'[Sale Date] <= MAX('Sales'[Sale Date])
)

2. Estimate the sales amount for the remaining days of the month:

EstimatedSalesAmount = 
SUMX(
    CALENDAR(
        MAX('Sales'[Sale Date]) + 1,
        EOMONTH(MAX('Sales'[Sale Date]), 0)
    ),
    [RegressionFormula]  // Replace [RegressionFormula] with your regression formula
)

3. Combine the real sales amount and the estimated sales amount in a single calculated measure:

PredictedTotalSalesAmount = 
[RealSalesAmount] + [EstimatedSalesAmount]

 Let me know if you might require any further assistance.