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

[Anonymous]

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	5.000	13,69
Grand Total	6112	16,74

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 date from the 16th of April is 1.500 per year an should be 259 days of sales with 4,11 (1500/365). 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?

[Anonymous]

Hi @Anonymous 

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.