Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Actuals vs Average + Chart Date Filtering

Hi all,   Having an issue with filtering a chart that contains a measure which subtracts a 3-month average of Actuals pre Project start date (Dec19) from current Actuals. Per screenshot below, w...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    Sorry for the late reply,try to use "dateadd" instead of "dateinperiod":

    AVG.PRE-REV =
CALCULATE(
[MRR(no RFS)]
, DATEADD( 'CALENDAR'[Date] , -3 , MONTH )&&
MAX( PROJECT_LOOKUP[RFS.DATE] )<=DATE(2020,11,31)))
/3

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi v-kelly-msft ,

Unfortunately this modification generates an error:

"A function 'DATESINPERIOD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

I tried adding a 'FILTER ( ALL (' to correct it but still came up with the same error.

I do think that the answer is somewhere in modifying one or more of these measures though

Any thoughts?

Kind regards,

Jake

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

 

Sorry for the late reply,try to use "dateadd" instead of "dateinperiod":

AVG.PRE-REV =
CALCULATE(
[MRR(no RFS)]
, DATEADD( 'CALENDAR'[Date] , -3 , MONTH )&&
MAX( PROJECT_LOOKUP[RFS.DATE] )<=DATE(2020,11,31)))
/3

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi v-kelly-msft,

    That measure still generates an error:

    "A function 'DATESADD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

     

     

    Jake