Same Period Last Year

[DeepakJha23]

Hi PBI Community,

I need some help with DAX Time Intelligence.

I am writing a DAX function that uses the sameperiodlastyear function on the calendar date to evaluate the total percentage in the 1 year period.

For example, the table A contains the percentages on Calendar_Date level and it is Fiscal Year 2021 data.

Fiscal Year 2021
Calendar_Date	Percentage
10/4/2020	1.50%
10/5/2020	2.60%
10/6/2020	3.70%
10/7/2020	4.80%
10/8/2020	5.50%
10/9/2020	5.40%
10/10/2020	2.30%

I am writing a DAX function for Fiscal Year 2022 as below to :
Previous_Year_Percentage = CALCULATE([Percentage], Sameperiodlastyear(DATE[Calendar_Date]),ALLEXCEPT(Date,[Calendar_Date])

I am getting the below output from the above DAX statement : 

Fiscal Year 2022
Calendar_Date	Percentage
10/3/2021	2.20%
10/4/2021	1.50%
10/5/2021	2.60%
10/6/2021	3.70%
10/7/2021	4.80%
10/8/2021	5.50%
10/9/2021	5.40%

The issue with the output is it takes 2.20% which is not part of the Fiscal Year 2021 and instead the output should start with 1.50% on 10/3/2021 and so on.

Like below : 

Fiscal Year 2022
Calendar_Date	Percentage
10/3/2021	1.50%
10/4/2021	2.60%
10/5/2021	3.70%
10/6/2021	4.80%
10/7/2021	5.50%
10/8/2021	5.40%
10/9/2021	2.30%

I would appreciate any help on this.

Thanks,
Deepak


@amitchandak @tamerj1 @lbendlin @Sahir_Maharaj @Ritaf1983 @MFelix @johnt75 

[ppm1]

Are you using MMDD or DDMM? Either way, instead of using time intelligence functions, one way would be to add Fiscal Year and a Day (or Month) of Fiscal Year columns to your Date table. You could then write a measure that moves the context to the previous Fiscal Year on the same Day of the the Fiscal year.

Pat

[DeepakJha23 · Accepted solution]

Hi Pat,

Thanks for responding.

I was able to resolve this using the below logic :

CALCULATE([Percentage],
DATEADD(Calendar_Date,-[Previous_Year_Calendar_Day_Count],DAY),
ALLEXCEPT(Date,[Calendar_Date])

-[Previous_Year_Calendar_Day_Count] moves the calendar days of current fiscal year to exact number of days based on previous year count.This helped in overlapping the fiscal year start date.

Thanks,

Thanks,
Deepak