Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create rolling 2,3 day Calculated column

I want to create a calculated column which will calulcated the sum of Num_Orders in the next 3 days for its respective account.

E.g. : Rollin3days for the first row will calculate sum for account 300707B for dates 4/2,4/3,4/4 wheather there an order or not.

The dates are not continous but I have a calender table with me.

 

I dont want a measure cause I want to use the calculated column for histogram.

Orders tables is as follows:

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    Try this calculated column formula

    =CALCULATE(SUM(Data[Num_orders]),FILTER(Data,Data[Account]=EARLIER(Data[Account])&&Data[Shippingdate]-EARLIER(Data[Shippingdate])<=3))

    Hope this helps.

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    Try this calculated column formula

    =CALCULATE(SUM(Data[Num_orders]),FILTER(Data,Data[Account]=EARLIER(Data[Account])&&Data[Shippingdate]-EARLIER(Data[Shippingdate])<=3))

    Hope this helps.