Calculated Column for sum between two dates

[jackj]

All,

I have a very simple data set with the following columns:

UserID

Week Ending

Week Ending - 28 Days (a calculated column that takes the week ending and subtracts 28 days)

Week Ending - 364 Days (a calculated column that takes the week ending and subtracts 364 days)

Subtotal Sales

I am looking to create a calculated column, not a measure, that takes the sum of the subtotal sales between two dates: Week Ending -364 Days and Week Ending -28 days for each user ID, and puts it in a calculated column on each row.

I'm struggling with the DAX for this - can anyone help?

[v-xiaotang]

Hi @jackj 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
really appreciate!
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,
Community Support Team _Tang

[v-xiaotang · Accepted solution]

Hi @jackj 

you can try this,

Calculated Column = CALCULATE(SUM('Table'[Subtotal Sales]),FILTER(ALL('Table'),EARLIER('Table'[Week Ending])<'Table'[Week Ending - 28 Days ]&&EARLIER('Table'[Week Ending])>='Table'[Week Ending - 364 Days]))

result

Best Regards,

Community Support Team _Tang

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

[VahidDM]

Hi @jackj 

It'd be better if you share sample of your data in a table format here, but please try this measure:

Sum = calculate(sum(table[Subtotal Sales]),filter(all(table),table[Week Ending]>=table[Week Ending - 364 Days]&&table[Week Ending]<=table[Week Ending - 28 Days]))

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!