Forum Discussion

vivivera's avatar
vivivera
New Member
5 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

joshyT7's avatar
joshyT7
Frequent Visitor
5 years ago

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