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 , understood re. the relationships, however even with the correct schema, the visual still shows all of 2019 and 2021 (albeit now with blanks).

I want to only show the months of 2020 up until November.

Thanks,

Jake

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

    Hi  Anonymous ,

     

    How about modifying your measure as below:

    AVG.PRE-REV =
CALCULATE(
[MRR(no RFS)]
, DATESINPERIOD( 'CALENDAR'[Date] , EDATE( MIN( PROJECT_LOOKUP[RFS.DATE] ), -1 ) , -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!