sameperiodlastyear() not working

[Jeanxyz]

Hi, I have created a measure to calculate current year amounts. This measure seems to work properly. When I wrap it with sameperiodlastyear, the new measure generates the same amount as the first measure. Please help.

//Deal Value New Subscription Current =

var curr_year=convert(year(today()),string)

var total_deal_value=calculate([Deal Value New Subscription won_progress],Dim_Start_Date[year]=curr_year)

Return

total_deal_value//

//Deal Value New Subscription 1yr back =

calculate([Deal Value New Subscription Current],SAMEPERIODLASTYEAR(Dim_Start_Date[Date]))//

[v-deddai1-msft]

Hi @Jeanxyz ,

How do your dim_date table and matrix look like? Did the dim_date table have relationship with the fact table? Your formula looks correct. It's better that you can show us sample pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Syndicate_Admin]

it does not work for you because the second formula (SAMEPERIODLASTYEAR) does not know what the selected date (year) is.

adds a filter to the sheet where the user selects the required year, that way both formulas recognize the filter and execute the correct value.

[Jeanxyz]

thanks, but I have removed year filter in the first measure. the time intelligence filter still doesn't work.

Deal Value EUR = sum(opportunity[Total Deal Value (€)])

Deal Value New Subscription 1yr back =

calculate([Deal Value EUR],dateadd(Dim_Start_Date[Date],-1,YEAR))

I then create a matrix table, adding Dim_Start_Date[Year] to the row field. the first measure works, but the 2nd measure still generates the same amounts.

I use data from Dynamics 365, could the data source cause the problem?

[Anonymous]

Instead of 

var curr_year=convert(year(today()),string)

try 

var curr_year=MAX(DateTable[Year])

where DateTabe is your calendar table with a Year column set to Date type.