Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a simple Forecasting-file where we register forecasts as below:
Customer Number | SKU | Units | Forecast Shipping Date | Status |
This is linked in PowerPivot (though relationships with the date and customer dimension tables) to a pricing table showing the various prices after discounts for different customers, Products (SKUs) and currently Year:
Customer Number | SKU | Price per unit | Year |
The formula I use to calculate revenue is pasted at the bottom.
Current scenario is that for some customers, the prices are not static for one particular year, and are valid from say, 1st April through 31st March the next year. Consequently, the revenue calculated for Q1 will be incorrect.
My initial thought was to replace the Year column with a Start and End dates, but I am not sure how I would incorporate this into the measure.
How would you set this up?
Measure:
Revenue =
VAR Revenue = SUMX (
f_Forecast,
f_Forecast[Units]
* CALCULATE (
MAX ('fAccount Overview'[Price per unit] ),
FILTER (
ALL (
'dAccount Overview'[SKU],
'dAccount Overview'[Customer Number],
'dAccount Overview'[Year]
),
'dAccount Overview'[SKU] = f_Forecast[SKU]
&& 'dAccount Overview'[Customer Number] = f_Forecast[Customer Number]
&& 'dAccount Overview'[Year] = YEAR (f_Forecast[Forecast Shipping Date] )
)
)
)
Return
IF(Revenue=BLANK(),0,Revenue)
@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi,
I have set up tables with dummy data. I cannot share the files, but I hope the tables, relationships and measures below make sense.
The idea is that we would need something similar to what's shown in table Account Overview (New Table), where we have a time period where this price is applied.
Please see below:
Tables
Forecast
Number | Customer | SKU | Units | Forecast Shipping Date |
1 | Customer 1 | 34288 | 22 | 2020-09-15 |
2 | Customer 2 | 34739 | 29 | 2020-05-31 |
2 | Customer 2 | 34786 | 125 | 2020-04-15 |
4 | Customer 4 | 35133 | 115 | 2020-10-30 |
5 | Customer 5 | 34829 | 29 | 2020-07-24 |
7 | Customer 7 | 34317 | 33 | 2020-05-18 |
7 | Customer 7 | 34317 | 51 | 2020-06-18 |
10 | Customer 10 | 34613 | 93 | 2020-08-08 |
12 | Customer 12 | 35005 | 99 | 2020-06-29 |
14 | Customer 14 | 35055 | 10 | 2020-09-16 |
14 | Customer 14 | 35148 | 118 | 2020-10-04 |
20 | Customer 20 | 34642 | 62 | 2020-07-27 |
20 | Customer 20 | 34380 | 23 | 2020-04-30 |
1 | Customer 1 | 34288 | 124 | 2021-04-26 |
2 | Customer 2 | 34739 | 63 | 2021-10-23 |
2 | Customer 2 | 34786 | 60 | 2021-01-15 |
2 | Customer 2 | 35344 | 71 | 2021-11-26 |
4 | Customer 4 | 35133 | 78 | 2021-02-19 |
14 | Customer 14 | 34821 | 75 | 2021-07-04 |
15 | Customer 15 | 34213 | 47 | 2021-10-25 |
19 | Customer 19 | 35234 | 18 | 2021-01-17 |
20 | Customer 20 | 34571 | 115 | 2021-07-26 |
Customer List
Number | Customer |
1 | Customer 1 |
2 | Customer 2 |
4 | Customer 4 |
5 | Customer 5 |
7 | Customer 7 |
10 | Customer 10 |
12 | Customer 12 |
14 | Customer 14 |
20 | Customer 20 |
15 | Customer 15 |
19 | Customer 19 |
Account Overview (Original Table)
Number | Customer | SKU | Price per unit | Year |
1 | Customer 1 | 34288 | $ 9,490.00 | 2020 |
2 | Customer 2 | 34739 | $ 7,144.00 | 2020 |
2 | Customer 2 | 34786 | $ 7,155.00 | 2020 |
4 | Customer 4 | 35133 | $ 610.00 | 2020 |
5 | Customer 5 | 34829 | $ 2,334.00 | 2020 |
7 | Customer 7 | 34317 | $ 1,051.00 | 2020 |
7 | Customer 7 | 34317 | $ 9,384.00 | 2021 |
10 | Customer 10 | 34613 | $ 9,574.00 | 2020 |
12 | Customer 12 | 35005 | $ 7,894.00 | 2020 |
14 | Customer 14 | 35055 | $ 1,564.00 | 2020 |
14 | Customer 14 | 35148 | $ 6,625.00 | 2020 |
20 | Customer 20 | 34642 | $ 9,906.00 | 2020 |
20 | Customer 20 | 34380 | $ 8,697.00 | 2020 |
1 | Customer 1 | 34288 | $ 7,533.00 | 2021 |
2 | Customer 2 | 34739 | $ 5,146.00 | 2021 |
2 | Customer 2 | 34786 | $ 791.00 | 2021 |
2 | Customer 2 | 35344 | $ 5,795.00 | 2021 |
4 | Customer 4 | 35133 | $ 9,960.00 | 2021 |
14 | Customer 14 | 34821 | $ 4,647.00 | 2021 |
15 | Customer 15 | 34213 | $ 9,488.00 | 2021 |
19 | Customer 19 | 35234 | $ 3,232.00 | 2021 |
20 | Customer 20 | 34571 | $ 4,284.00 | 2021 |
Account Overview (New Table)
Number | Customer | SKU | Price per unit | Start | End |
1 | Customer 1 | 34288 | $ 9,490.00 | 1/1/2020 | 12/31/2020 |
2 | Customer 2 | 34739 | $ 7,144.00 | 1/1/2020 | 12/31/2020 |
2 | Customer 2 | 34786 | $ 7,155.00 | 1/1/2020 | 12/31/2020 |
4 | Customer 4 | 35133 | $ 610.00 | 4/1/2020 | 3/31/2021 |
5 | Customer 5 | 34829 | $ 2,334.00 | 4/1/2020 | 3/31/2021 |
7 | Customer 7 | 34317 | $ 1,051.00 | 4/1/2020 | 3/31/2021 |
7 | Customer 7 | 34317 | $ 9,384.00 | 1/1/2020 | 12/31/2020 |
10 | Customer 10 | 34613 | $ 9,574.00 | 1/1/2020 | 12/31/2020 |
12 | Customer 12 | 35005 | $ 7,894.00 | 1/1/2020 | 12/31/2020 |
14 | Customer 14 | 35055 | $ 1,564.00 | 1/1/2020 | 12/31/2020 |
14 | Customer 14 | 35148 | $ 6,625.00 | 1/1/2020 | 12/31/2020 |
20 | Customer 20 | 34642 | $ 9,906.00 | 1/1/2020 | 12/31/2020 |
20 | Customer 20 | 34380 | $ 8,697.00 | 1/1/2020 | 12/31/2020 |
1 | Customer 1 | 34288 | $ 7,533.00 | 1/1/2021 | 12/31/2021 |
2 | Customer 2 | 34739 | $ 5,146.00 | 1/1/2021 | 12/31/2021 |
2 | Customer 2 | 34786 | $ 791.00 | 1/1/2021 | 12/31/2021 |
2 | Customer 2 | 35344 | $ 5,795.00 | 1/1/2021 | 12/31/2021 |
4 | Customer 4 | 35133 | $ 9,960.00 | 4/1/2021 | 3/31/2022 |
14 | Customer 14 | 34821 | $ 4,647.00 | 1/1/2021 | 12/31/2021 |
15 | Customer 15 | 34213 | $ 9,488.00 | 1/1/2021 | 12/31/2021 |
19 | Customer 19 | 35234 | $ 3,232.00 | 1/1/2021 | 12/31/2021 |
20 | Customer 20 | 34571 | $ 4,284.00 | 1/1/2021 | 12/31/2021 |
+ Standard Date Table
Relationships
Date[Date] - Forecast[Forecast Shipping Date] (1-to-many)
Customer List[Number] - Forecast[Number] (1-to-many)
Customer List[Number] - Account Overview[Number] (1-to-many)
Measure
VAR Revenue = SUMX (
Forecast,
Forecast[Units]
* CALCULATE (
MAX ('Account Overview'[Price per unit] ),
FILTER (
ALL (
'Account Overview'[SKU],
'Account Overview'[Number],
'Account Overview'[Year]
),
'Account Overview'[SKU] = Forecast[SKU]
&& 'Account Overview'[Number] = Forecast[Number]
&& 'Account Overview'[Year] = YEAR (Forecast[Forecast Shipping Date] )
)
)
)
Return Revenue
Output in pivot table
Revenue | Year | ||
2020 | 2021 | ||
Customer | SKU | ||
Customer 1 | 34288 | 208780 | 934092 |
Customer 10 | 34613 | 890382 | |
Customer 12 | 35005 | 781506 | |
Customer 14 | 34821 | 348525 | |
35055 | 15640 | ||
35148 | 781750 | ||
Customer 15 | 34213 | 445936 | |
Customer 19 | 35234 | 58176 | |
Customer 2 | 34739 | 207176 | 324198 |
34786 | 894375 | 47460 | |
35344 | 411445 | ||
Customer 20 | 34380 | 200031 | |
34571 | 492660 | ||
34642 | 614172 | ||
Customer 4 | 35133 | 70150 | 776880 |
Customer 5 | 34829 | 67686 | |
Customer 7 | 34317 | 88284 |
Thank you,
Daniel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |