Prediction for the rest of the year. Knowing the start date and the sales of 12 month period.

[Mr_flyf]

Hi,
I have this data set, that shows my sales opportunities.
The assumption is that the sales is equal each day which means (1.500 / 365 = 4,11 per day).

Start date	Vol	Pr. day
16-04-2024	1.500	4,11
17-04-2024	1.750	4,79
18-04-2024	200	0,55
24-04-2024	600	1,64
03-06-2024	1.250	3,42
01-07-2024	293.617	804,43
Grand Total	298.917	818,95

I am searching for a sollution so I can make a waterfall that shows the opportunities per month the rest of the year.
That means that the sales of 16.04 is 1.500 per year an should be 259 days of sales with 4,11. From the 17th there shoud be added on 4,79 per day ect.

Below you can see my excel version of the case. That should be correctly as far as I know but making this into DAX - what to do?

[Mr_flyf]

Hi. According to my calculations the monthly numbers should be:

So the month will count the figures from the start date.

Does it make sense?

[v-nuoc-msft · Accepted solution]

Hi @Mr_flyf 

For your question, here is the method I provided:

Here's some dummy data

"Table"

If there is no date table in the model, you need to create one.

"Date"

Date = CALENDAR("1/1/2024", "12/31/2024")

In the "Table", create a measure to determine daily sales based on start date and quantity.

Daily Sales = 
VAR StartDate = SELECTEDVALUE('Table'[Start date])
VAR EndDate = MAX('Date'[Date])
VAR DaysRemaining = DATEDIFF(StartDate, EndDate, DAY)
RETURN DIVIDE(SELECTEDVALUE('Table'[Vol]), DaysRemaining, 0)

Create a measure to calculate monthly sales. This metric will summarize daily sales for each month.

Monthly Sales = 
SUMX(
    FILTER(
        ALL('Date'),
        'Date'[Date] >= MIN('Table'[Start date]) 
        && 'Date'[Date] <= DATE(2024,12,31)
    ),
    [Daily Sales]
)

After calculating monthly sales, you can create a waterfall chart in Power BI.

Regards,

Nono Chen

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

[Mr_flyf]

Anyone have the sollution with the shown figures?