Creating A Prior Year Amount Column

[reh169]

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 difficult that I had thought it would be. 

Here is my measure for PY: 

Total Est Rev PY = CALCULATE([Total Est Rev],sameperiodlastyear('Calendar'[Date]))

Here is what I am trying to do for the column and it is not working.

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

I am getting the error that my filter has not enough arguments that were passed to it, that it needs at minium 2 functions and I thought I did have 2 functions.

Any help would be most appreciated.

[amitchandak]

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)

[amitchandak]

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

[reh169]

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

[kentyler]

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/

[reh169]

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 · Accepted solution]

Hi @reh169 ，

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