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

SAMEPERIODLASTYEAR returns Blank

I have data that looks like this:

 

Cut-off DateAux_DateCategorySales
23.01.201801.01.2018A1200
24.01.201901.01.2019A8900
25.01.202001.01.2020A6400
23.01.201801.01.2018B1100
24.01.201901.01.2019B5900
25.01.202001.01.2020B2100
23.01.201801.01.2018C4800
24.01.201901.01.2019C2600
25.01.202001.01.2020C1300

 

For a selected Cut-off Date, I want to show the sum of sales for both the selected date and the date one year prior. My issue now is that I can't just use SAMEPERIODLASTYEAR on the Cut-off Date, since the exact day changes from year to year (in 2018, it's the 23rd; in 2019, it's the 24th). I figured I could just add an auxiliary column that contains the first of each month rather than the actual cut-off date (see column Aux_Date). I built two simple measures:

 

Sum_Sales = SUM(Data[Sales])

 

and

 

Sum_Sales_LY = CALCULATE([Sum_Sales], SAMEPERIODLASTYEAR(Data[Aux_Date])).

 

But when I put these on my dashboard, Sum_Sales_LY will show (Blank), if I filter on Cut-off Date:

 

image.png

 

It works fine when I filter on Aux_Date, but that's not what I want:

 

image.png

Is there any way to utilize SAMEPERIODLASTYEAR without having to switch to Aux_Date as filter?

 

Maybe I'm struggling because I can't quite wrap my head around as to why SAMEPERIODLASTYEAR is a valid input for the <filter> argument of the CALCULATE formula. The documentation suggests that SAMERPERIODLASTYEAR returns a single column of data (i.e. the dates incremented by -1 year), and I don't understand how that serves as a filter to anything.

1 ACCEPTED SOLUTION
Regular Visitor

I actually found the solution myself. Changing the measure to the following does the trick:

Sum_Sales_LY =
var ly = SAMEPERIODLASTYEAR(Data[Aux_Date])
return CALCULATE([Sum_Sales], all(Data[Cut-off Date]), Data[Aux_Date]=ly)

View solution in original post

2 REPLIES 2
Regular Visitor

I actually found the solution myself. Changing the measure to the following does the trick:

Sum_Sales_LY =
var ly = SAMEPERIODLASTYEAR(Data[Aux_Date])
return CALCULATE([Sum_Sales], all(Data[Cut-off Date]), Data[Aux_Date]=ly)

View solution in original post

Super User IV
Super User IV

@DanielM16 , Please use the date table in all such cases

 

Year behind Sales = CALCULATE([Sum_Sales],dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE([Sum_Sales],SAMEPERIODLASTYEAR('Date'[Date]))

 

Refer to my video why TI fails: https://www.youtube.com/watch?v=OBf0rjpp5Hw

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors