Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating A Prior Year Amount Column

I have a measure that calculates the prior year amount for Est Rev. I want to use the KPI Power Matrix and to do that I need to have the prior year Est. Rev. in a column. This proving to be more diff...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    I‘m not sure whether your measure [Total Est Rev PY] checked right?I'm guessing whether you're using a  calendar date to calculate the value?If so, you need to choose a table date instead of a calendar date,then you will see as below:

     

     

    As for the measure

     

    Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))

     

     

    Should be corrected as below:

     

     

    Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate])-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))

     

    You missed a ")" after the function "Year“，if there still has errors,check what I have said above, I guess the problem happens in the "date" you are choosing.

     

    Best Regards,
    Kelly

kentyler's avatar
kentyler
Solution Sage
6 years ago

Total Est. Rev PY =

   CALCULATE(
      [Total Est Rev],
      Filter(<table expression> missing, 
         PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&&'Quotes Estimates US'[Job Start Month]='Quotes Estimates US'[Job Start Month]
               )
      )
you seem to have just one expression in your filter function, although it is a complex expression and uses && to combine 2 columns you want to filter

and you are missing the <table> argument to filter

https://dax.guide/filter/

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Ok so now it is saying that hte filter function exepcts a table expression for arguement '1', but a string or numeric expression was used. 

Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))
  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi Anonymous ，

     

    I‘m not sure whether your measure [Total Est Rev PY] checked right?I'm guessing whether you're using a  calendar date to calculate the value?If so, you need to choose a table date instead of a calendar date,then you will see as below:

     

     

    As for the measure

     

    Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate]-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))

     

     

    Should be corrected as below:

     

     

    Total Est. Rev PY = CALCULATE([Total Est Rev],Filter('Quotes Estimates US'[Estimate Revenue],PREVIOUSYEAR('Calendar'[Year])=YEAR('Quotes Estimates US'[JobSDate])-1)&& 'Calendar'[Month]='Quotes Estimates US'[Job Start Month]))

     

    You missed a ")" after the function "Year“，if there still has errors,check what I have said above, I guess the problem happens in the "date" you are choosing.

     

    Best Regards,
    Kelly