Forum Discussion

jackj's avatar
jackj
Helper I
4 years ago
Solved

Calculated Column for sum between two dates

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?

 

 

Need Help
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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  !!

     

     

     

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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