SamePeriodLastYear/DateAdd not working for Leap day

[Anonymous]

Hi,

I am trying to calculate YoY Sales , and have a date table below, my sales granularity is monthly and hence date table is filtered accordingly

dim_Time = FILTER (

CALENDAR (Date(2018, 1, 1), Date(2025,12,31)),

[Date] = EOMONTH([Date],0)

)

The revenue logic for displaying YoY sales is not working for Feb 21 as previous year was a leap day. It is working fine for other dates.

Revenue_YOY = CALCULATE([Revenue_BM], SAMEPERIODLASTYEAR(dim_Time[Date]))

or

Revenue_YOY = CALCULATE([Revenue_BM], DATEADD(dim_Time[Date],-1,YEAR))

See sample below - Data starts from Jan 2020.

Date	Revenue_BM	Revenue_YOY
1/31/2020	622250105.7
2/29/2020	546077853.2
3/31/2020	538997128.2
4/30/2020	330526012.1
5/31/2020	508803027.4
6/30/2020	677496344.6
7/31/2020	706124018.4
8/31/2020	710193083.3
9/30/2020	745988041.9
10/31/2020	777762536.2
11/30/2020	753081905.1
12/31/2020	774521707.4
1/31/2021	742936044.3	622250105.7
2/28/2021	666638393.5

.

I tried exploring first date option as well, but the result is the same

SoM = DATE(YEAR(dim_Time[Date]),MONTH(dim_Time[Date]),1)

SoM	Revenue_BM	Revenue_YOY
1/1/2020	622250105.7
2/1/2020	546077853.2
3/1/2020	538997128.2
4/1/2020	330526012.1
5/1/2020	508803027.4
6/1/2020	677496344.6
7/1/2020	706124018.4
8/1/2020	710193083.3
9/1/2020	745988041.9
10/1/2020	777762536.2
11/1/2020	753081905.1
12/1/2020	774521707.4
1/1/2021	742936044.3	622250105.7
2/1/2021	666638393.5

Is there any eay else I can model this? for the Feb -21 data to work?

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

Please check the below picture and the sample pbix file's link down below.

In my opinion, it is better to put another column from the Dim-Date table into visualization.

the left picture is that I put the actual date column.

the right picture is that I put endofmonth column.

https://www.dropbox.com/s/96had6ixbxo4pkv/brianalva.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous · Accepted solution]

Hi Jihwan,

I am using the KPI to calculate YoY Revenue, so I changed my time dimension table itself to start of the month and was able to get the required results.

dim_Time = FILTER (

    CALENDAR (Date(2018, 1, 1), Date(2025,12,31)),

    [Date] = DATE(YEAR([Date]),MONTH([Date]),1)

)