Average last 30, 90 days on a daily basis

[Anonymous]

Hey lovely community,

I have been a PowerBI user for years now, but for the past couple of weeks I have been trying to build things myself - I have to say that DAX isn't my favourite thing so far (probably related to learning pains from having to switch from SQL).

This migh be a really trivial question, but I'm struggling with the AVERAGE logic in DAX:

• Objective: add a measure that calculates the average of the past 30 days everyday ;

• I have tried a lot of your suggestions here (even tried the Quick Measure -"rolling average"), but I don't seem to be able to have the same results as if I was simply doing it on Excel: when I apply any average (of the past 30 days) logic, I have results from day 1, when I only expect values from the 31st day.

• If I do a simple sum of the past 30 days, I do start getting values from the 31st day, so I don't think this is a calendar issue.

The table I'm working with is pretty simple: client ref; cash; date (I created a calendar table as well, that is linked to this table, which I'm using for any dates logic I need).

The original table has 3 columns and I want to see "30 day avg" as a new column (measure). As final output, this is what I expect:

Date	AgreementRef	Cash balance	30 day average
01/01/2019	ABC	33115
02/01/2019	ABC	37517
03/01/2019	ABC	-5429
04/01/2019	ABC	-1309
05/01/2019	ABC	-6189
06/01/2019	ABC	78021
07/01/2019	ABC	71572
08/01/2019	ABC	20278
09/01/2019	ABC	66166
10/01/2019	ABC	51486
11/01/2019	ABC	-4501
12/01/2019	ABC	34579
13/01/2019	ABC	34201
14/01/2019	ABC	63244
15/01/2019	ABC	-11193
16/01/2019	ABC	-4699
17/01/2019	ABC	48626
18/01/2019	ABC	35979
19/01/2019	ABC	40465
20/01/2019	ABC	24711
21/01/2019	ABC	-10764
22/01/2019	ABC	51573
23/01/2019	ABC	98659
24/01/2019	ABC	21137
25/01/2019	ABC	69028
26/01/2019	ABC	65687
27/01/2019	ABC	67765
28/01/2019	ABC	27576
29/01/2019	ABC	84303	37296.69
30/01/2019	ABC	56685	37942.97
31/01/2019	ABC	48015	38439.63
01/02/2019	ABC	57797	39115.63
02/02/2019	ABC	23292	40073
03/02/2019	ABC	99018	43417.23
04/02/2019	ABC	53172	45395.93
05/02/2019	ABC	54902	44625.3
06/02/2019	ABC	9451	42554.6
07/02/2019	ABC	20863	42574.1
08/02/2019	ABC	85353	43213.67

For the calendar table design I used the following expression:

Dates 5 =
  GENERATE (
    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday    
    VAR currentDay = [Date]
    VAR days = DAY( currentDay )
    VAR months = MONTH ( currentDay )
    VAR years = YEAR ( currentDay )
    VAR nowYear = YEAR( TODAY() )
    VAR nowMonth = MONTH( TODAY() )
    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
    VAR todayNum = WEEKDAY( TODAY() )
    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
  RETURN ROW (
    "day", days,
    "month", months,
    "year", years,
    "day index", dayIndex,
    "week index", weekIndex,
    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
    "year index", INT( years - nowYear )
  )
)

Thank you for all the great content you have here, by the way - it is a great place to learn 🙂 

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Measure = 
CALCULATE (
    SUM ( 'Table'[Cash] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date] < MAX ( 'Calendar'[Date] )
            && 'Table'[Date]
                >= MAX ( 'Calendar'[Date] ) - 31
    )
)

See my attached pbix file.

Best Regards,

Giotto

[coathangers]

Heroes don't always wear capes

edit. unless you do, which is fine no judgement

[amitchandak]

@Anonymous , try with date dimension

Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],MAX(Sales[Sales Date]),-30,Day))

Rolling 90 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],MAX(Sales[Sales Date]),-90,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Greg_Deckler]

If you could provide some sample data and expected result that would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Updated!

[v-gizhi-msft]

Hi,

Did my last reply solve your issue?

If so, please mark it as a solution for others to see.

Thanks!

Best Regards,

Giotto

[Anonymous]

Hey @v-gizhi-msft ,

None of the solutions worked, unfortunately 😞

I think it's because I'll always have to filter the data to this year - I will try that and let you know!

Thank you so much!

[v-gizhi-msft]

Hi,

If you still have issue later, could you please share your file by OneDrive for Business?

And remember to remove any sensitive data.

This will let me help you further.

Expect your reply!

Best Regards,

Giotto

[Anonymous]

Hi @v-gizhi-msft ,

What finally worked for me:

Running 30D = 

CALCULATE([Total AVG Cash], 
 Filter (all ('Calendar'), 
 'Calendar'[Date].[Date] > max ('Calendar'[Date].[Date])-30 &&
 'Calendar'[Date].[Date] <= MAX('Calendar'[Date].[Date]) )
)

I realised that I didn't want "sum" because I'm working with cash balance and it doesn't make sense to have a sum, so I created a measure called Total AVG.

I am not sure why I can see value for the first 30 days of the year (not having privious year data), but at least I have the correct amounts showing from the 30th of the month 🙂

Thank you so much for your help!

[v-gizhi-msft]

Hi,

If my reply has helped you solve your issue, please mark it as a solution for others to see.

Thanks!

Best Regards,

Giotto

[Mariusz]

Hi @Anonymous 

Try using DATESINPERIOD()
https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn