Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Mr_flyf
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 dateVolPr. day
16-04-20241.5004,11
17-04-20241.7504,79
18-04-20242000,55
24-04-20246001,64
03-06-20241.2503,42
01-07-20245.00013,69
Grand Total611216,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?

Mr_flyf_0-1715088548862.pngMr_flyf_1-1715088567056.png

1 REPLY 1
v-nuoc-msft
Community Support
Community Support

Hi @Mr_flyf 

 

For your question, here is the method I provided:

 

Here's some dummy data

 

"Table"

 

vnuocmsft_0-1712632469989.png

 

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

 

"Date"

 

vnuocmsft_1-1712632486419.png

 

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)

 

vnuocmsft_2-1712632641806.png

 

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.

vnuocmsft_3-1712632731840.png

 

Regards,

Nono Chen

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

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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