Forum Discussion

kotarosai's avatar
kotarosai
Icon for Helper II rankHelper II
6 years ago
Solved

Rolling Average: Stop Date

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 ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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's avatar
kotarosai
Icon for Helper II rankHelper II
6 years ago

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