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
a68tbird
Resolver II
Resolver II

Difficulty with SAMEPERIODLASTYEAR

Hello All -

  Having some problem wrapping my head around the SAMEPERIODLASTYEAR function.  After having much difficulty actually trying to get anything but a blank value, this article helped me very much: same period last year using SAMEPERIODLASTYEAR return nothing.  Surrogate keys in the date dimension table do not work with the SAMEPERIODLASTYEAR function. 

 

However, what I am getting returned now is the entire year of sales, and that's not what I was expecting.  The measure that I have is:

 

Sales LY = CALCULATE([Sales YTD],SAMEPERIODLASTYEAR(CalendarTable[Date]))

where the [Sales YTD] measure is:

Sales YTD = TOTALYTD(SUM(OrderItem[PriceExclTax]),CalendarTable[Date])

I assumed that SAMEPERIODLASTYEAR would filter on the [Sales YTD] measure, but it's not. Should I be including a further filter on my [Sales LY] measure?

 

Thanks in advance for the assistance.

 

1 ACCEPTED SOLUTION
kcantor
Community Champion
Community Champion

@a68tbird

ARe you putting in the months on the axis so that the formula has a way to break down the measure? It is doing exactly what the DAX say. It is calculating year to date sales. When you add same period last year, your measure still requests the year to date total. You would need to specify start and end dates within your same period measure to create a difinitive cut off for totals.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
kcantor
Community Champion
Community Champion

@a68tbird

ARe you putting in the months on the axis so that the formula has a way to break down the measure? It is doing exactly what the DAX say. It is calculating year to date sales. When you add same period last year, your measure still requests the year to date total. You would need to specify start and end dates within your same period measure to create a difinitive cut off for totals.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Bah! Of course! I was simply comparing values on card visuals, but once I plot them on a line chart, it works. 

 

Thanks!

 

@a68tbird you could potentially force it to work on a card by adding a visual level filter to the card that gives it a date context. My standard date table for instance has a column called YearDiff and a column called DayDiff. If I added two visual level filters to a card, DateTable[YearDiff] = 0 and DateTable[DayDiff] <= 0, that would make the card always start with a date context of all the days between January 1 of the current year until today.

 

To recreate those columns in your own table:

 

DateDiff = INT(DateTable[Date] - TODAY())

 

YearDiff = INT(YEAR(DateTable[Date]) - YEAR(TODAY()))

 

The past is negative numbers, the present is 0, and the future is positive. I leave MonthDiff as an exercise to the reader.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.