cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Same Period Last Year

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

1 ACCEPTED SOLUTION
Helper I

Hi Pat,

Thanks for responding.

I was able to resolve this using the below logic :

CALCULATE([Percentage],
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

2 REPLIES 2
Solution Sage

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

Microsoft Employee
Helper I

Hi Pat,

Thanks for responding.

I was able to resolve this using the below logic :

CALCULATE([Percentage],
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