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

[vivivera]

Hi all,

I was trying to calculate 7 days Rolling average PowerBi and here is my codes:

Trt7dayRA =

    VAR __LAST_DATE = LASTDATE('Torontodaily'[Date].[Date])

    RETURN

        AVERAGEX (

            DATESBETWEEN('Torontodaily'[Date].[Date], DATEADD(__LAST_DATE, -6, DAY), __LAST_DATE),

            CALCULATE(SUM([Trttoday]))

        )

But the output is always the same number as my input [Trttoday], like the following table shows:

Date	Trttoday	Trt7dayRA
12/20/2020	345	345
12/19/2020	453	453
12/18/2020	222	222

My guessing is because we are using a calculated column as the reference, but I am not sure.

Could one of you guys help me?

Much appreciate.

Thanks,

Vera

[joshyT7]

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

[Ashish_Mathur]

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.

[Anonymous]

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

[Anonymous · Accepted solution]

Hi @vivivera 

I think your dax may work in measure. If you want to build a calcualted column, you can try my way.

C.ROlling 7 Days Avg =
AVERAGEX (
    FILTER (
        Torontodaily,
        Torontodaily[Date]
            >= EARLIER ( Torontodaily[Date] ) - 6
            && Torontodaily[Date] <= EARLIER ( Torontodaily[Date] )
    ),
    Torontodaily[Trttoday]
)

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@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.

[littlemojopuppy]

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
        )
    )