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.
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
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |