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. 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

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.