Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michaelstone
Helper I
Helper I

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

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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

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.

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

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.

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

 

Thanks @v-piga-msft 

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

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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