cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brianalva
Helper I
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
Revenue_YOY = CALCULATE([Revenue_BM], DATEADD(dim_Time[Date],-1,YEAR))
See sample below - Data starts from Jan 2020.
 
DateRevenue_BMRevenue_YOY
1/31/2020622250105.7 
2/29/2020546077853.2 
3/31/2020538997128.2 
4/30/2020330526012.1 
5/31/2020508803027.4 
6/30/2020677496344.6 
7/31/2020706124018.4 
8/31/2020710193083.3 
9/30/2020745988041.9 
10/31/2020777762536.2 
11/30/2020753081905.1 
12/31/2020774521707.4 
1/31/2021742936044.3622250105.7
2/28/2021666638393.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)
 
SoMRevenue_BMRevenue_YOY
1/1/2020622250105.7 
2/1/2020546077853.2 
3/1/2020538997128.2 
4/1/2020330526012.1 
5/1/2020508803027.4 
6/1/2020677496344.6 
7/1/2020706124018.4 
8/1/2020710193083.3 
9/1/2020745988041.9 
10/1/2020777762536.2 
11/1/2020753081905.1 
12/1/2020774521707.4 
1/1/2021742936044.3622250105.7
2/1/2021666638393.5

 

 

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

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

View solution in original post

2 REPLIES 2
Jihwan_Kim
Community Champion
Community Champion

Hi, @brianalva 

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.

 

Picture2.png

 

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


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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.