cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michaelstone Frequent Visitor
Frequent Visitor

Strange behaviour with SAMEPERIODLASTYEAR

Hi,

 

I'm building a Power BI report which is analysing PoS transaction data so have a fact table (Transactions) with every individual transaction including timestamp and sales amount (Transactions[Sales]).

 

I've added a custom column for the transaction date (Transactions[Date]) which is joined to a calendar table (Calendar) which is generated from MIN(Transactions[Date]) to MAX(Transactions[Date]).

 

I've created measures for

 

Total Sales = SUM(Transactions[Sales]
Total Sales Last Year = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))

 

The problem occurs when I include the last date in the Calendar table when calculating Total Sales Last Year.

 

As you can see below, if I select 13 - 14 July 2019 the calculations are correct i.e. the Total Sales Last Year are for the corresponding 2 days in 2018.

 

Note the the Date slider is Calendar[Date] and the Date column in the table is Transactions[Date].

 

13-14 July.PNG

 

 

 

 

 

 

 

 

 

 

If I change this to be 14 - 15 July 2019 where 15 July is the last date for any transactions, the Total Sales Last Year is incorrectly calculated for the period 14 - 30 July.

14-15 July.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have found a workaround which involves adding some additional days to the end of my Calendar table but the problem will occur again if these future days are selected in my date filter.

 

Am I using this function incorrectly or is there a bug?

 

Thanks,

 

Michael

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Strange behaviour with SAMEPERIODLASTYEAR

Hi @michaelstone ,

Sorry for my mistake.

Please modify the measure Total Sales - Previous Year  like below.

Total Sales - Previous Year =
SUMX (
    'Calendar',
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
)

Here is the output.

Capture.PNG

Best  Regards,

Cherry

 

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Community Support Team
Community Support Team

Re: Strange behaviour with SAMEPERIODLASTYEAR

Hi @michaelstone ,

Based on your information, I have made a test with your formula, I cannot reproduce your issue.

Untitled.png

If it is convenient, could you share your data sample and so that we could help further on it?

You also could have a test on my test sample which has been attached below.

Best  Regards,

Cherry

 

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
michaelstone Frequent Visitor
Frequent Visitor

Re: Strange behaviour with SAMEPERIODLASTYEAR

Hi @v-piga-msft ,

 

Here is a link to a file which shows the issue.

 

PBIX File

 

Note that the end date is now 17 July so you need to have this as the end date to see the issue.

 

Thanks,

 

Michael

Community Support Team
Community Support Team

Re: Strange behaviour with SAMEPERIODLASTYEAR

Hi @michaelstone ,

By my test and research based on your pbix, I'm afraid that you should create the table with the date column from Calendar table like below so that you could get the correct output.

Untitled.png

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
suryaburaboyina Frequent Visitor
Frequent Visitor

Re: Strange behaviour with SAMEPERIODLASTYEAR

Hi,

 

It is working fine wheni tried, if didnot set the Date columns with same format please do that.

 

Below is hte screenshot for same.

 

SamplePeriodlastyear.PNG

 

mark it correct, if its useful.

 

Thanks,

Surya Teja

michaelstone Frequent Visitor
Frequent Visitor

Re: Strange behaviour with SAMEPERIODLASTYEAR

Hi @v-piga-msft ,

My production PBIX does use the Calendard[Date] column but as you can see from your example, the Total at the bottom of the Total Sales - Previous Year column is still incorrect.

I was only using Transactions[Date] in the visualisation because it showed the underlying problem i.e. that the SAMEPERIODLASTYEAR is not working correctly and is including dates to the end of the month.

Thanks,

Michael

michaelstone Frequent Visitor
Frequent Visitor

Re: Strange behaviour with SAMEPERIODLASTYEAR

Hi @suryaburaboyina ,

 

I don't understand what you mean by "did not set Date column with same format".

 

As per my instructions, the problem only occurs when you include the last day of data in the slicer i.e. 17 July 2019.

 

Thanks,

 

Michael

 

 

Community Support Team
Community Support Team

Re: Strange behaviour with SAMEPERIODLASTYEAR

Hi @michaelstone ,

Sorry for my mistake.

Please modify the measure Total Sales - Previous Year  like below.

Total Sales - Previous Year =
SUMX (
    'Calendar',
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
)

Here is the output.

Capture.PNG

Best  Regards,

Cherry

 

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

michaelstone Frequent Visitor
Frequent Visitor

Re: Strange behaviour with SAMEPERIODLASTYEAR

This seems like a workaround for an underlying bug but will accept as the solution.

 

Thanks @v-piga-msft 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 276 members 3,090 guests
Please welcome our newest community members: