cancel
Showing results for
Did you mean:
Helper I

## SamePeriodLastYear/DateAdd not working for Leap day

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
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?

1 ACCEPTED SOLUTION
Helper I

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)
)
2 REPLIES 2
Community Champion

Hi, @brianalva

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.

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.

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.

Best regards, JiHwan Kim

Helper I

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)
)

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors