Column that can SUMIF for Last X number of days AND for values within its own source column

[GooseHelpful199]

I am trying to build a Column in DAX that allows me to Add the last 3 Days worth of Sales for each ID. More specifically, I want this column to SUM the Sales by filtering the ID column for only results with the same ID column, and then have a condition along the lines of AND('Query1'[Day]<= Whatever the Day in that row is, 'Query1'[Day]>=  Whatever the date in that row is minus 3). "Last 3 Weeks of Sales"  below is an example of the column I'm trying to build.

[lbendlin · Accepted solution]

Best to use the WINDOW functions for this. Make sure you present the data aggregated by ID and Date.

or alternatively

[Kedar_Pande · Accepted solution]

Last 3 Days Sales = 
VAR CurrentDay = 'Query1'[Day]
VAR CurrentID = 'Query1'[ID]
RETURN
CALCULATE(
SUM('Query1'[Sales]),
FILTER(
'Query1',
'Query1'[ID] = CurrentID &&
'Query1'[Day] <= CurrentDay &&
'Query1'[Day] >= CurrentDay - 2
)
)

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande

[GooseHelpful199]

This worked perfectly as well, thanks!

[lbendlin · Accepted solution]

Best to use the WINDOW functions for this. Make sure you present the data aggregated by ID and Date.

or alternatively

[GooseHelpful199]

Sorry I should have been more specific, but the example that I was showing is supposed to represent the Query, not the visual. The Sales figures you see should not be a Sum of Sales, just the raw data of the Sales. I am looking to have it as a Column because I have existing measures that Im hoping I can simply change to re-direct to this new column.

Is there a way to alter the formula so that it would work with how my raw data was presented? I tried using that formula but ran into an issue with duplicated rows being encountered in WINDOW's Relation parameter.

EDIT: I was able to adjust it to work to my needs, thanks for the help!