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

Try

Total Est Rev PY = CALCULATE([Total Est Rev],dateadd('Calendar'[Date],-1,year))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

This still results in an empty column for my Total Est Rev PY.