Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate with a date range?

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:
YearQuarterMonthDayGeographyPromo Units CYUPC
2019Qtr 1January27Ahold Delhaize Giant Carlisle Division-RMA - Food41000
2019Qtr 1February3Ahold Delhaize Giant Carlisle Division-RMA - Food6261001
2019Qtr 1February3Ahold Delhaize Giant Carlisle Division-RMA - Food8021002
2019Qtr 1February3Ahold Delhaize Giant Carlisle Division-RMA - Food6401003
2019Qtr 1February10Ahold Delhaize Giant Carlisle Division-RMA - Food6441001
2019Qtr 1February10Ahold Delhaize Giant Carlisle Division-RMA - Food7501002
2019Qtr 1February10Ahold Delhaize Giant Carlisle Division-RMA - Food5761003

 

Trade Plan 2019: planned promoted sales units by promotion program

 

Start DateEnd DatePromo EventGeographyPlanned Promo Units CYUPC
1/1/201902/30/2019Ahold Q1 PromoAhold Delhaize Giant Carlisle Division-RMA - Food1001000
1/2/201902/30/2020Ahold Q1 PromoAhold Delhaize Giant Carlisle Division-RMA - Food2001001
1/3/201902/30/2021Ahold Q1 PromoAhold Delhaize Giant Carlisle Division-RMA - Food1001002
1/4/201902/30/2022Ahold Q1 PromoAhold Delhaize Giant Carlisle Division-RMA - Food2501003
1/5/201902/30/2023Ahold Q1 PromoAhold Delhaize Giant Carlisle Division-RMA - Food3001001
1/6/201902/30/2024Ahold Q1 PromoAhold Delhaize Giant Carlisle Division-RMA - Food4001002
1/7/201902/30/2025Ahold Q1 PromoAhold Delhaize Giant Carlisle Division-RMA - Food2501003
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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])))

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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])))