Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DeepakJha23
Helper I
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_DatePercentage
10/4/20201.50%
10/5/20202.60%
10/6/20203.70%
10/7/20204.80%
10/8/20205.50%
10/9/20205.40%
10/10/20202.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_DatePercentage
10/3/20212.20%
10/4/20211.50%
10/5/20212.60%
10/6/20213.70%
10/7/20214.80%
10/8/20215.50%
10/9/20215.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_DatePercentage
10/3/20211.50%
10/4/20212.60%
10/5/20213.70%
10/6/20214.80%
10/7/20215.50%
10/8/20215.40%
10/9/20212.30%


I would appreciate any help on this.

Thanks,
Deepak


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

 

 

1 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

View solution in original post

2 REPLIES 2
ppm1
Solution Sage
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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.