YTD and SAMEPERIODLASTYEAR

[mthomas1973]

Hello all. I hope someone can help. 

I am dipping my toes into time intelligent function for the first time and have a result I can't understand. 

I have a couple of measures: 

ytd actual = TOTALYTD([Actual],'oa FactPeriodEndBalance'[PeriodEndDate],"31/03")

and

py actual = calculate([Actual],SAMEPERIODLASTYEAR('oa FactPeriodEndBalance'[PeriodEndDate]))

I then use these in table that looks like this 

 

Everything works great (there are also other sections, all of which behave as I expected). The oddity is that Fair Value Adj figure. The amount sits in the period 31/3/2021 so should be in the previous financial year not this one?

It is worth noting that the total (2,089K) in the matrix ignores that Fair Value amount and is the sum of sales and interest only.

Am I missing something obvious? Any help is appreciated.

[amitchandak · Accepted solution]

@mthomas1973 , prefer to use date table

ytd actual = TOTALYTD([Actual],'Date'[Date],"31/03")

Last ytd actual = TOTALYTD([Actual],dateadd('Date'[Date],-1, year) ,"31/03")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[mthomas1973]

That's great thank you. Yes, seems to be behaving more normally now -- really appreciate the steer