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. 

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

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.