Calculate with a date range?

[Anonymous]

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:  

CALCULATE([Promo Units CY],FILTER('Trade Plan 2019','Trade Plan 2019'[Start Date])>'Date'[Date],FILTER('Trade Plan 2019','Trade Plan 2019'[End Date])<'Date'[Date])

Actual Promoted SAles Units Table:

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

[v-yuta-msft]

@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.

[Anonymous]

@v-yuta-msft  the formula didn't bring in any values - it's all blank

[Anonymous · Accepted solution]

I solved this with a "Datesbetween" formula

Actual Promo Units = CALCULATE([Promo Units CY], DATESBETWEEN('Date'[Date],MAX('Trade Plan 2019'[Start Date]),max('Trade Plan 2019'[End Date])))