topic Re: Same Period Last Year in DAX Commands and Tips

Same Period Last Year

DeepakJha23 — Wed, 05 Jul 2023 05:11:52 GMT

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

Re: Same Period Last Year

ppm1 — Thu, 06 Jul 2023 11:48:04 GMT

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

Re: Same Period Last Year

DeepakJha23 — Fri, 07 Jul 2023 06:42:12 GMT

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