Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?
Solved! Go to 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.
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.
User | Count |
---|---|
121 | |
69 | |
66 | |
56 | |
52 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |