Forum Discussion

DeepakJha23's avatar
DeepakJha23
Helper I
3 years ago
Solved

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 

 

 

DAX Time Intelligence
  • DeepakJha23's avatar
    DeepakJha23
    3 years ago

    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

2 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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's avatar
      DeepakJha23
      Helper I
      3 years ago

      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