CODEWITHSUNDEEP
dax
Roger Steinberg
Roger Steinberg

Reputation: 1604

Calculate the average for the last 30 days excluding today. DAX

I would like to calculate the average revenue for the last 30 days (not including today) I tried the following formula but the amount calculated is incorrect:

CALCULATE(
           AVERAGE(table[Revenue]),
           FILTER(table,DATEADD(table[date],-30,DAY))
    )

How can I exclude today in the average? If i wanted to compare that result with the 30 days before that (i.e between -30 days and -60days) should i use datesinperiod?

Upvotes: 1

Views: 4410

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

The DATESBETWEEN function is the most intuitive to me.

Previous30DayAverage =
VAR CurrentDate = LASTDATE(table[date]) --Or TODAY() or whatever you choose
RETURN
CALCULATE(
    AVERAGE(table[Revenue]),
    DATESBETWEEN(table[date], CurrentDate - 30, CurrentDate - 1)
)

I think you can see how to tweak this for -30 to -60 days.

Upvotes: 2

Related Questions