cancel
Showing results for 
Search instead for 
Did you mean: 
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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors