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, when I add a Date filter with advanced filter 'is on or after' Dec19, I still see all of 2019 and 2021.

I only want to see Dec19 to today's date (Nov20).

VALUE MEASURE

 

BRR.VS.AVG =
IF(
MAX( PROJECT_LOOKUP[RFS.IND] ) = "No"
, 0
, IF(
MAX( 'CALENDAR'[Date] ) < MAX( PROJECT_LOOKUP[RFS.DATE] )
, 0
, [MRR(no RFS)] - [AVG.PRE-REV]
))

 

SUPPORTING MEASURES

 

MRR(no RFS) =
CALCULATE(
SUM( REVENUE[USD_BUDGET_AMT] )
, REVENUE[GL_REVENUE_CATEGORY_CD] = "BRR"
|| REVENUE[GL_REVENUE_CATEGORY_CD] = "Billable Run Rate"
)

 

AVG.PRE-REV =
CALCULATE(
[MRR(no RFS)]
, DATESINPERIOD( 'CALENDAR'[Date] , EDATE( MIN( PROJECT_LOOKUP[RFS.DATE] ), -1 ) , -3 , MONTH )
)
/3

 

PBIX FILE 

 

 

 

 

 

 

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!

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    v-kelly-msft thanks for the response.

     

    I still have the issue that the months are still visible outside of the range I want to see (i.e. all of 2019 and 2021).

     

    Also, in my actual data model I have other tables linked to the 'CALENDAR' table and 'PROJECT_LOOKUP' tables so that I receive this error when I try to change that relationship to 'Both':

     

  • 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