DATESINPERIOD based on dynamic date range AND second dynamic criteria

[coast2coast]

Hello - this is my first post here and I am new to PowerBI, so please bear with me 🙂 Thank you!

I am trying to dynamically sum a value based on a changing date range (3 weeks +/- current row date)

I also need it to have a criteria to only sum rows for that date range for a specific person (ID)

I saw that DATESINPERIOD could get me close to what I need but not sure how to incorporate the 2nd criteria as well so it doesn't sum the values in column A for specific dates for everyone instead of just the ID for the row we are on. 

Example: Looking for new column value = Sum of Days +/- 3 weeks based on corp id and week = row

[coast2coast]

@Anonymous thank you for the response. In your solution, where is the ID criteria determined? I need to be able to slice on both ID and Week where the table could have up to 90K distinct IDs with values for every week. 

Also - I keep getting "not enough memory" errors when i try to do even a simple measure like this one, any tips for that? I can't limit my data any more than it is already. 

[Anonymous · Accepted solution]

Hi @coast2coast ,

Below is my table:

The following DAX might work for you:

Measure = 
 var result = 
  CALCULATE(
    SUMX('Table' , 'Table'[Days]),
    DATESINPERIOD('Table'[Week], MIN('Table'[Week]) , 21, DAY )
)
return result

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.