YOY Cycle Time metric using SAMEPERIODLASTYEAR creating future dates, throwing data off.

[tjlundquist1]

I've created a table with 4 measures, comparing current cycle time to that of the previous year's. It works as intended, however - I only have up to Jan 2021 data in there but the SAMEPERIODLASTYEAR function is creating future dates in the current cycle time measure as seen below based on the value existing for the prior year. 

I am trying to figure out what to add to my measure so that the "Assgn to Val Last Year" measure only calculates when the date (Year column) is <= Today. I've tried a few variations with no luck. 

Any advice?? Measures below. 

Thanks! 

Column 1: 

Avg Assgn to Val = AVERAGE('CCC Timeline'[CT AssgnSent to Valutation])

Column 2:

CALCULATE([Avg Assgn to Val],SAMEPERIODLASTYEAR('CCC Timeline'[Assgn Sent Date].[Date]))

Column 3:

Assgn to Val YOY = [Avg Assgn to Val]-[Assgn to Val Last Year]

Column 4:

DIVIDE([Assgn to Val YOY],[Assgn to Val Last Year])

[v-kelly-msft]

Hi @tjlundquist1 ,

Have you tried the steps suggested by @Ashish_Mathur ?If not,could you pls upload your .pbix file to one drive business and share the link with us?This would help to find the reason.

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

Follow these steps:

1. Create a Calendar Table and created calculated columns to extract Year, Month name and Month number
2. Sort the Month Name by the Month number
3. Create a relationship from the Assgn Sent Date column to the Date column of the Calendar Table
4. To your visual, drag the Year and Month name column from the Calendar Table
5. Write this measures
   1. if(isblank([Avg Assgn to Val]),blank(),CALCULATE([Avg Assgn to Val],SAMEPERIODLASTYEAR(calendar[date])))