7 days rolling average Dax function output are the same value as the original input

Hi vivivera, 

As littlemojopuppy mentioned, you will want to use CALCULATE, DATESINPERIOD and also LASTDATE to create your rolling average measure. This is a better method than using a calculated column. 

It is also advisable to use a date table for this as well. 

I have a video on my YouTube channel that walks through the steps. Take a look and let me know if you have any questions at all. 


You can find the video here: https://youtu.be/mCm7QtNFf1Y

Thanks! 

Josh

Hi Vera -

Here's the DAX I use for a rolling seven day total for Active Users.  You want to substitute an expression for AVERAGE() where I have [Active Users]

    CALCULATE(
        [Active Users],
        DATESINPERIOD(
            'Calendar'[Date],
            LASTDATE('Calendar'[Date]),
            -7,
            DAY
        )
    )

vivivera , if you have date with time stamp then create a date like

Date = [Datetime].date

or
Date = date(year([Datetime]),month([Datetime]),day([Datetime]))

Prefer not to use .date in time intelligence. Also use a date table

Try measure like one of these. In display use date from date table  ( change 6 to 7 , if needed)

Rolling 7 = divide(CALCULATE(sum('Torontodaily'[Trttoday]),DATESINPERIOD('Date'[Date ],MAX(Torontodaily[Date]),-6,DAY)) ,
CALCULATE(distinctCOUNT('Date'[Date]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-6,DAY), not(isblank(('Torontodaily'[Trttoday])))))

Rolling 7 = divide(CALCULATE(sum('Torontodaily'[Trttoday]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,DAY)) ,

or
CALCULATE(distinctCOUNT('Date'[Date]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-6,DAY), not(isblank(('Torontodaily'[Trttoday])))))

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 :radacad sqlbi My Video Series Appreciate your Kudos.

Hi vivivera 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

Hi,

Create a Calendar Table and establish a relationship from the the Date column in the Toronto Table to the Date column in the Calendar Table.  To your visual, drag the Date column from the Calendar table.  I have assumed that Trttoday is a column in the Torontodaily table.  Write these measures:

Measure 1 = SUM('Torontodaily'[Trttoday])

Measure 2 = calculate([Measure 1],datesbetween(calendar[date],min(calendar[date])-6,min(calendar[date])))

Hope this helps.