Rolling Average: Stop Date

[kotarosai]

I feel this is close, but please find the formula for a 20 day rolling average below. The measure actually works great and is accurate, except for the fact that when plotted on a line chart, it goes past beyond the [Latest Date Loaded] which is also posted below. Any idea on why it does not cut off on the [Latest Date Loaded]? The 'Sales Result'[CAS_Bonus_date__c] is related to the date table field of 'Date Table'[Date] as well. I appreciate your feedback, thanks!

Total Sales = SUM('Sales Result'[Net_Sales_Units__c])

Latest Date Loaded = VAR X = SUMMARIZE(ALL('Sales Result'),'Sales Result'[Account],"M",CALCULATE(MAX('Sales Result'[CAS_Bonus_Date__c]),'Sales Result'[RecordTypeId]="0126g000000O4VdAAK",'Sales Result'[Region__c]<>"Specialty Pharmacy"))
RETURN MAXX(x,[M])

4 Week Moving Average Chart = 
CALCULATE(
    [Total Sales],
    DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]),-20,DAY),
    FILTER(ALLSELECTED('Date Table'),'Date Table'[Working Day] = 1 &&
    'Date Table'[Date] <= [Latest Date Loaded]))
    /
CALCULATE(
    DISTINCTCOUNT('Date Table'[Date]),
    DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]),-20, DAY),
    FILTER(ALLSELECTED('Date Table'),'Date Table'[Working Day] = 1 &&
    'Date Table'[Date] <= [Latest Date Loaded]))

 

[v-lili6-msft · Accepted solution]

hi @kotarosai 

You need to create a IF conditional in the measure as below:

4 Week Moving Average Chart =

IF(

MAX('Sales Result'[CAS_Bonus_date__c])<=[Latest Date Loaded]

&&MAX('Sales Result'[CAS_Bonus_date__c])<>BLANK(),

CALCULATE(

[Total Sales],

DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]),-20,DAY),

FILTER(ALLSELECTED('Date Table'),'Date Table'[Working Day] = 1 &&

'Date Table'[Date] <= [Latest Date Loaded]))

/

CALCULATE(

DISTINCTCOUNT('Date Table'[Date]),

DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]),-20, DAY),

FILTER(ALLSELECTED('Date Table'),'Date Table'[Working Day] = 1 &&

'Date Table'[Date] <= [Latest Date Loaded])))

or

4 Week Moving Average Chart =

IF(

MAX('Sales Result'[CAS_Bonus_date__c])<=[Latest Date Loaded]

&&MAX('Sales Result'[CAS_Bonus_date__c])<>BLANK(),

CALCULATE(

[Total Sales],

DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]),-20,DAY),

FILTER(ALLSELECTED('Date Table'),'Date Table'[Working Day] = 1 &&

'Date Table'[Date] <= [Latest Date Loaded]))

/

CALCULATE(

DISTINCTCOUNT('Date Table'[Date]),

DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]),-20, DAY),

FILTER(ALLSELECTED('Date Table'),'Date Table'[Working Day] = 1 &&

'Date Table'[Date] <= [Latest Date Loaded])))

Regards,

Lin

[kotarosai]

Goodness, I was way overthinking it. Thanks for your support Lin!

[kotarosai]

Please let me know if there are any other details that may help troubleshoot as well. Thanks!