Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Last 30 Day Cumulative Moving Average

Hi All,

 

I have got below table and have been trying to get average number of Line backlogs the last 30 days with no luck of resolution.

 

Backlog - Line was genereted on or before the specified date and Line Email Date is future date.

 

Objective: Get Average number of back logs in the last 30days. On a particular day, number of backlogs would be cumulative sum of Line_Flag which meets above condition for back log. and then average it out for 30 days. I am trying to show the number by Sales_Rep.

 

For Ex: If today is 26Sep2022. It should take time period from 27Aug2022 to 26Sep2022 and sum all the line_flag which were back logs for 27Aug2022 irrespective when that was generated (Line_Date), for 28Aug2022 like wise for each day and divide it by 30 to get the average.

 

Dax Tried:

 


_QUOTE Line Backlog Last 30DM =
AVERAGEX(calculatetable(values(LINES[Line_DATE]),DATESINPERIOD(LINES[Line_DATE],max(LINES[Line_DATE]),-30,DAY)),
CALCULATE(
sum(LINES[Line_Flag]),
Filter(ALLEXCEPT(LINES,LINES[SALES_REP]) ,
(LINES[Line_DATE]<=max(LINES[Line_DATE])) &&
((LINES[Line_Email_Date]>max(LINES[Line_DATE])) ||
(LINES[QUOTE_EMAIL_DATETIME] = BLANK())))))


 

 

Any help would be appreciated.

 

Regards

Raj Kumar

Need Help

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous Try something like this:

     

    Better Rolling Average = 
    VAR __EndDate = MAX('Table'[Date])
    VAR __StartDate = __EndDate - 30
    VAR __Table = 
            FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate),
RETURN
    AVERAGEX(__Table,[__Value])