I'm trying to do what appears to be the simplest of calculations but is bamboozling me nonetheless.
I've got a calculated filtered table that has the relevant info I need from another larger, SQL data table.
I've got a date & time column and I've got a numbers column. There are multiple rows that contain the same date, but every row has a unique date & time.
I just want to add a column that displays a 7-day rolling... anything. I've kind of managed to get a 7-day sum but it seems to be a case of 'sum everything between date&time(max) and date&time(x)' where the very first instance of date&time(x) is a date&time(max)-7 but after that it's just all data between date&time(max) and current row.
Any idea how I can make it do the same but between current row and current row - 7 days?
Formula I've got so far (and bear in mind it's taken me two days to get this far - previously it was just copying the individual row values across to the new column...):
7 Day Sum =
VAR startdate = 'calctable'[collectiondt] - 7
VAR result =CALCULATE(
SUM('calctable'[result]),
FILTER(
'calctable',
'calctable'[collectiondt] >= startdate)
)
RETURN
resultI'm still learning myself so can't give you an easy formula but looking at some community posts it looks like a measure with the DATESINPERIOD function is what you want?
e.g this but replacing average with sum or whatever calculation you need. https://community.powerbi.com/t5/Desktop/Moving-Average/m-p/43041
I don't fully understand the requirement but would grouping by or change type functions help at all? Left click column header etc. Good. Luck.
I got it to work, not sure it's 100% correct in the most efficient way of doing this.
You'll need a date table (i.e. singular dates) to use the DATEADD function, to do this add a new table using this DAX: DateTable = CALENDAR(min(calctable[collectiondt]),max(calctable[collectiondt]))
I then created a duplicate of the [collecitondt] column, but without time: LINKDATE = DATE(calctable[collectiondt].[Year],calctable[collectiondt].[MonthNo],calctable[collectiondt].[Day]) and created a relationship to the Date column in the date table.
I then added a column to the DateTable using this: Last 7 Day Sum = Last 7 Day Sum = CALCULATE(sum(calctable[result]),DATESBETWEEN(DateTable[Date],DATEADD(DateTable[Date],-7,DAY),DateTable[Date])).
In your display table you'll need to use the Date from DateTable, but you can add collectiondt in there too, but you'll see duplicated numbers of the last 7 day sum per time entry.
Hope this helps.
RM.
rogermoore - that's perfect! Thank you very much!
Already had a date table set up, never thought to do the calculations in there. And it was a damn sight more efficient than what I was trying to do with cumulative summing minus 7 day offset cumulative summing.
It'll display nicely in a table, but matrix and line graph view will only display summarised numbers, i.e.average of 7 Day Sum, count of 7 Day Sum... Is that just a limitation of PowerBI I'll have to live with?
I think you want a measure for this rather than a calculated column.
Calculated column is applied to each row of data while a measure is calculated at the level of the visual.
So you create a measure with the calculation you want, then use the measure as the value on your visual.
IME the key to getting PowerBI to work well is to have a really solid data model behind it, and manage the relationships between the tables yourself rather than accepting the default data model that it builds. I've helped people with reports where they had assumed there was a relationship between two tables but they were actually linked on a completely different field, so when you built any measures etc nothing worked. It's a great tool when it works but massively frustrating and obscure when it doesn't.
It’ll display nicely in a table, but matrix and line graph view will only display summarised numbers, i.e.average of 7 Day Sum, count of 7 Day Sum… Is that just a limitation of PowerBI I’ll have to live with?
Are you wanting to display the conditiondt as the axis and display each of your results as one value and the Last 7 Day figure as another, but showing one result for the Last 7 days per day?
RM.
