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