cancel
Showing results 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

## Sameperiodlastyear - Leap Year

-I have a report with 3 columns (Date, System Sales, System Sales LY)

-The goal is to compare Day to Day Sales from this year to Last year.

-I have used the basic DAX for the Sales calculation

System Sales = SUM(Salestable[Total])

System Sales LY = CALCULATE ([System Sales], SAMEPERIODLASTYEAR('DateDimension'[Date])

-My 'Sales table' is joined to the 'Date Dimension table' on SalesDate and Date respectively.

-My problem is Feb2020. It is a leap year. So for LY calculation, DAX is auto-populating for '2/29'. Even though there is no '2/29' in 2019. (Please see the attached screenshot for reference.).

- In LY System Sales column, for the '2/29' should be empty, however, it shows the value of '2/28'. (In this case, you can see the value \$449,003.6 repeating twice)

1 ACCEPTED SOLUTION
Anonymous
Not applicable
This is the correct behaviour. This is how it should be. The last day of the month is compared to the last day of the month of the prior year. This is what time-intel means. If you want to override this, you'll need to write your own piece of logic that'll deal with Feb 29. Rather easy to do.
6 REPLIES 6
New Member

A possible option for this is to create a calculated column in your date table to flaf Feb 29 as a Leap Day. Then use the code below for setting last year numbers to 0 for Feb 29 .

VAR LeapDay = IF( HASONEVALUE( 'Date'[Date] ), MAXA( 'Date'[LeapDay] ) )
RETURN
SWITCH(
TRUE( ),
LeapDay = 1, 0,
CALCULATE( Sales , SAMEPERIODLASTYEAR( 'Date'[Date] ))
)

Anonymous
Not applicable
``````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],
)
)
return
__result
)``````
Resolver II

Im curious if you would instead use the DATEADD opposed to SPLY if it would be any different. I have had issues with SPLY in the past and since have used DATEADD instead.

Lets try this first - as SPLY may have logic built in for leap year.. which i suppose folks off of leap year would like to know what they made last year if there was a leap year for the 28th... anywho this is a silly oversight imo by microsoft possibly.

Double check your data to ensure there are no values for 02/29/2019 - I understand this date technically doesnt exist..

Anywho -

``````Rev. LY =
CALCULATE( [TOTAL REV], DATEADD( dDATES, -1 , YEAR) )``````

let me know if there are any differences.

Hi Alex, Thanks for the quick response.

-I have tried the DATEADD. It gave me the same outcome as the 'Sameperiodlastyear'

-I called it 'SystemSales LY_2'. The DAX formula is: System Sales LY_2 = CALCULATE([System Sales],DATEADD(DateDimension[Date],-1,YEAR))
Please see the attached screenshot for reference.
-Just to be sure, I have checked both 'Sales table' and 'Date dimension table. Neither tables have the date '2/29/2019' as it does not exist. Thus there is no data associated for that date '2/29/2019'. However, I believe due to the leap year of 2/29/2020, PBI is auto-populating value for it.

Anonymous
Not applicable
By the way... You should never rely on the automatic Date table that PBI creates for you. NEVER. You should always create your own Date table according to the rules for such tables.
Anonymous
Not applicable
This is the correct behaviour. This is how it should be. The last day of the month is compared to the last day of the month of the prior year. This is what time-intel means. If you want to override this, you'll need to write your own piece of logic that'll deal with Feb 29. Rather easy to do.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors