Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! 🙂
Solved! Go to Solution.
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.
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.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |