Forum Discussion

Limerick's avatar
Limerick
Frequent Visitor
3 years ago
Solved

SUM dynamic / dependend on date

Hello together, 

 

I have a table like this:

What I need to do now, is to sum the amount for every row over the past 5 days, like this:

So basically, sum everything up, where the date is 5 days younger, then the current date.

The result hast to be in a column, not a measure.
Can you please help me out? Thank you a lot in advance! 🙂 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Create an Index column in the Query Editor and write this calculated column formula

    Column = CALCULATE(SUM(Data[Amount]),FILTER(Data,Data[Date]>=EARLIER(Data[Date])-4&&Data[Date]<=EARLIER(Data[Date])&&Data[Index]<=EARLIER(Data[Index])))

    Hope this helps.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Create an Index column in the Query Editor and write this calculated column formula

    Column = CALCULATE(SUM(Data[Amount]),FILTER(Data,Data[Date]>=EARLIER(Data[Date])-4&&Data[Date]<=EARLIER(Data[Date])&&Data[Index]<=EARLIER(Data[Index])))

    Hope this helps.

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Power BI aggregates by default. You will need to add an index column to your data.

     

    your expected outcome does not match the sample data (Feb 5 is missing)

     

    Does "last five days" include or exclude the current date?

    test = DATATABLE("Index",INTEGER,"Date",DATETIME,"Amount",CURRENCY,{
    {1,dt"2023-02-01",100},
    {2,dt"2023-02-01",100},
    {3,dt"2023-02-02",120},
    {4,dt"2023-02-02",150},
    {5,dt"2023-02-04",120},
    {6,dt"2023-02-05",100},
    {7,dt"2023-02-07",200},
    {8,dt"2023-02-08",250}
    }
    )

Last five days = 
var i=[Index]
var d=[Date]
return CALCULATE(sum([Amount]),all(test),test[Index]<=i,test[Date]<=d,test[Date]>=d-5)