cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

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

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?

Community Support

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors