cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
2 REPLIES 2
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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)``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.