Forum Discussion

coast2coast's avatar
coast2coast
New Member
2 years ago
Solved

DATESINPERIOD based on dynamic date range AND second dynamic criteria

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

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • coast2coast's avatar
    coast2coast
    New Member
    2 years ago

    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.