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

amitchandak's avatar
amitchandak
Super User
6 years ago

Do you have Job start date . then Try

 

Total Est Rev PY = CALCULATE([Total Est Rev],dateadd('Calendar'[Date],-1,year))
var _maxDate =maxx(dateadd('Quotes Estimates US','Quotes Estimates US'[JobSDate],-1,Year))
var _min_date =minx(dateadd('Quotes Estimates US','Quotes Estimates US'[JobSDate],-1,Year))

// OR this one if date is selected from calendar
/*
var _maxDate =maxx(dateadd('Calendar','Calendar'[Date],-1,Year))
var _min_date =minx(dateadd('Calendar',Calendar'[Date],-1,Year))


*/
return
= CALCULATE([Total Est Rev],
Filter('Quotes Estimates US','Quotes Estimates US'[JobSDate] >=_min_date =minx && 'Quotes Estimates US'[JobSDate]<=_maxDate)