Forum Discussion

kotarosai's avatar
kotarosai
Helper 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
v-lili6-msft's avatar
v-lili6-msft
Community Support
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
    Helper II
    6 years ago

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