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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
CertainlyMe
New Member

SamePeriodLastYear returns an empty colum but with a correct total

Hi everyone 🙂

 

First steps with Power BI here. 

 

I'm working on road accidents, trying to build a table to compare the number of accidents for every month of 2022 with the same month of 2021. 

 

I work from a very simple table (Table_Accidents) that contains a line for every accident and elements on these accidents (date, place, type, etc.). 

 

 

So I did the following :

I made a DAX formula to count the number of lines, which is my total number of accidents : 

 

Nb_Accidents = COUNT(table_Accidents[#])

"#" being the name of the column where I just apply a different number to a new accident.

 

 

Then I wrote this formula : 

Nb_accidents_Y-1 = CALCULATE(COUNT(table_Accidents[#]));SAMEPERIODLASTYEAR(Table_Accidents[date]))
 
Then I build a simple table with these two items, and I get close to where I want , except for my Y-1 column, the column is completely empty, but the total is here and correct (?!)
 
Same if I try with a SUM of accident costs, for instance: I get an empty column but with a correct total...
 
 
If anyone has a clue on this...
 
Have a nice day 🙂
Jim
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @CertainlyMe 

SAMEPERIODLASTYEAR is a time intelligence function that requires a proper date table with unique and contiguous date values. Create a date table snd create a relationship between the date table snd your table then you can use 'Date'[Date] in SAMEPERIODLASTYEAR 

View solution in original post

2 REPLIES 2
CertainlyMe
New Member

Hey @tamerj1 

 

Yess it worked ! 

 

Thank you very much for your help !

 

tamerj1
Super User
Super User

Hi @CertainlyMe 

SAMEPERIODLASTYEAR is a time intelligence function that requires a proper date table with unique and contiguous date values. Create a date table snd create a relationship between the date table snd your table then you can use 'Date'[Date] in SAMEPERIODLASTYEAR 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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