Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to replicate what a "SUMIFS" forumla would do in excel with a date range. I have 2 main data tables one that has actual promoted sales units and one that has planned promoted sales units by promotion program based on a start date and end date.
I have relationships set up to a date table for both of them as well as a product table (by UPC).
I would like to calculate the actual sales units for the time period between start and end date in the planned sales units table.
I tried writing this formula but it's not working:
Year | Quarter | Month | Day | Geography | Promo Units CY | UPC |
2019 | Qtr 1 | January | 27 | Ahold Delhaize Giant Carlisle Division-RMA - Food | 4 | 1000 |
2019 | Qtr 1 | February | 3 | Ahold Delhaize Giant Carlisle Division-RMA - Food | 626 | 1001 |
2019 | Qtr 1 | February | 3 | Ahold Delhaize Giant Carlisle Division-RMA - Food | 802 | 1002 |
2019 | Qtr 1 | February | 3 | Ahold Delhaize Giant Carlisle Division-RMA - Food | 640 | 1003 |
2019 | Qtr 1 | February | 10 | Ahold Delhaize Giant Carlisle Division-RMA - Food | 644 | 1001 |
2019 | Qtr 1 | February | 10 | Ahold Delhaize Giant Carlisle Division-RMA - Food | 750 | 1002 |
2019 | Qtr 1 | February | 10 | Ahold Delhaize Giant Carlisle Division-RMA - Food | 576 | 1003 |
Trade Plan 2019: planned promoted sales units by promotion program
Start Date | End Date | Promo Event | Geography | Planned Promo Units CY | UPC |
1/1/2019 | 02/30/2019 | Ahold Q1 Promo | Ahold Delhaize Giant Carlisle Division-RMA - Food | 100 | 1000 |
1/2/2019 | 02/30/2020 | Ahold Q1 Promo | Ahold Delhaize Giant Carlisle Division-RMA - Food | 200 | 1001 |
1/3/2019 | 02/30/2021 | Ahold Q1 Promo | Ahold Delhaize Giant Carlisle Division-RMA - Food | 100 | 1002 |
1/4/2019 | 02/30/2022 | Ahold Q1 Promo | Ahold Delhaize Giant Carlisle Division-RMA - Food | 250 | 1003 |
1/5/2019 | 02/30/2023 | Ahold Q1 Promo | Ahold Delhaize Giant Carlisle Division-RMA - Food | 300 | 1001 |
1/6/2019 | 02/30/2024 | Ahold Q1 Promo | Ahold Delhaize Giant Carlisle Division-RMA - Food | 400 | 1002 |
1/7/2019 | 02/30/2025 | Ahold Q1 Promo | Ahold Delhaize Giant Carlisle Division-RMA - Food | 250 | 1003 |
Solved! Go to Solution.
I solved this with a "Datesbetween" formula
@Anonymous ,
You can modify your measure as below:
result = CALCULATE ( [Promo Units CY], FILTER ( 'Trade Plan 2019', 'Trade Plan 2019'[Start Date] > RELATED ( 'Date'[Date] ) && 'Trade Plan 2019'[End Date] < RELATED ( 'Date'[Date] ) ) )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I solved this with a "Datesbetween" formula
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |