Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have created a calculated table that contains two fields: a date (no time), and a numerical value for each date.
It looks something like this:
I am trying to get a running average of the previous 30 days for each date. I have tried the following columns:
DIVIDE( CALCULATE( SUM(DaySum[Cargo Tonnes]), DATESBETWEEN( 'DaySum'[Date], DATEADD('DaySum'[Date],-30,DAY), 'DaySum'[Date] ) ),30)
AND
DIVIDE( CALCULATE( SUM(DaySum[Cargo Tonnes]), DATESINPERIOD ( 'DaySum'[Date], DaySum[Date],-30,DAY )),30)
AND
DIVIDE( CALCULATE( SUM(DaySum[Cargo Tonnes]), DATESBETWEEN( DaySum[Date], FIRSTDATE(DATEADD(DaySum[Date],-30,DAY)), LASTDATE(DaySum[Date]) ) ),30)
But the result is always the [Cargo Tonnes] field divided by 30. It never seems to SUM the preceeding 30 days, then divide that sum by 30, thus:
Can anyone suggest where I might be going wrong?
Thanks!
Tom.
Solved! Go to Solution.
Hey Tom,
By including a filter, ALL(DaySum), you will achieve the results you are looking for. See below for an example.
30 Day Average = DIVIDE( CALCULATE( SUM(DaySum[Cargo Tonnes]), ALL(DaySum), DATESBETWEEN(DaySum[Date], LASTDATE(DaySum[Date])-30, LASTDATE(DaySum[Date])) ) ,30)
Hey Tom,
By including a filter, ALL(DaySum), you will achieve the results you are looking for. See below for an example.
30 Day Average = DIVIDE( CALCULATE( SUM(DaySum[Cargo Tonnes]), ALL(DaySum), DATESBETWEEN(DaySum[Date], LASTDATE(DaySum[Date])-30, LASTDATE(DaySum[Date])) ) ,30)