topic Re: Leap Year issue with SAMEPERIODLASTYEAR in DAX Commands and Tips

Leap Year issue with SAMEPERIODLASTYEAR

dataandme — Thu, 22 Aug 2024 03:36:57 GMT

Hi,
Im new to Power BI and DAX. Really appreciate for some help. I used SAMEPERIODLASTYEAR function to refer back to the Sales Last Year. But Im getting the 28th Feb 2023 sales value reflected as last year's sales for 29th Feb 2024 even though the day 29th Feb 2023 doesnot exists. Is there a work around this to display that value blank when such dates doesnot exists?

Thanks in advance.

Re: Leap Year issue with SAMEPERIODLASTYEAR

raji_n — Thu, 22 Aug 2024 05:07:43 GMT

dataandme this is the expected behaviour, sameperiodlastyear compares day with day hence 29th is considered, please refer this DAX to handle 29th data,

Sales PY :=
if( hasonevalue(DateDimension[Date]),
var __currentDate = max(DateDimension[Date])
var __shouldCalculate =
month(__currentDate) <> 2
&& day(__currentDate) <> 29
var __result =
if(__shouldCalculate,
CALCULATE(
[System Sales],
DATEADD(DateDimension[Date],-1,YEAR)
)
)
return
__result
)

Re: Leap Year issue with SAMEPERIODLASTYEAR

dataandme — Thu, 22 Aug 2024 06:34:00 GMT

Hi raji_n,
Thank you for the DAX, but now the entire month of Feb is vanishing rather than just 29th Feb.

Re: Leap Year issue with SAMEPERIODLASTYEAR

raji_n — Thu, 22 Aug 2024 06:35:35 GMT

dataandme try =2 instead of <>2 in the DAX, my bad, sorry!

Re: Leap Year issue with SAMEPERIODLASTYEAR

dataandme — Fri, 23 Aug 2024 02:54:40 GMT

Hi raji_n
Now, its skipping 29 Feb 2024 which is good but giving me only the PY Sales for Feb.