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

Top Solution Authors