Forum Discussion
Same Period Last Year with Filter
I simply want to return results from last year based on the number of months that have passed this year. So this year would be January through October have passed. I want to get last years results...
GA LY = var m = max('Calendar'[CalendarDate]) return CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date]),'Calendar'[Calendar Date]<=EDATE(m,-12))lbendlin Found this on Marc Russo's blog, if anyone needs a solution. This works:
PY Last Day Selection = VAR LastDaySelection = LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds] ) VAR CurrentRange = DATESBETWEEN ( 'Calendar'[Calendar Date], MIN ( 'Calendar'[Calendar Date] ), LastDaySelection ) VAR PreviousRange = SAMEPERIODLASTYEAR ( CurrentRange ) RETURN IF ( LastDaySelection > MIN ( 'Calendar'[Calendar Date]),CALCULATE ( [Gross Adds], PreviousRange ))
I can't use file sharing, because our company blocks all of that. I couldn't paste 2024 because the limit 20,000 characters. But if you place 20 as the value for the whole year in 2023 and 21 for whole year 2024, you'll see that by using measure the result will be 7300 for the whole year instead of 6080:
GA LY = var m = max('Subscriber Activity'[Date]) return CALCULATE(SUM('Subscriber Activity'[Gross Adds]),SAMEPERIODLASTYEAR('Subscriber Activity'[Date]),'Subscriber Activity'[Date]<=EDATE(m,-12))
| Gross Adds | Date |
| 20 | 1/1/2023 |
| 20 | 1/2/2023 |
| 20 | 1/3/2023 |
| 20 | 1/4/2023 |
| 20 | 1/5/2023 |
| 20 | 1/6/2023 |
| 20 | 1/7/2023 |
| 20 | 1/8/2023 |
| 20 | 1/9/2023 |
| 20 | 1/10/2023 |
| 20 | 1/11/2023 |
| 20 | 1/12/2023 |
| 20 | 1/13/2023 |
| 20 | 1/14/2023 |
| 20 | 1/15/2023 |
| 20 | 1/16/2023 |
| 20 | 1/17/2023 |
| 20 | 1/18/2023 |
| 20 | 1/19/2023 |
| 20 | 1/20/2023 |
| 20 | 1/21/2023 |
| 20 | 1/22/2023 |
| 20 | 1/23/2023 |
| 20 | 1/24/2023 |
| 20 | 1/25/2023 |
| 20 | 1/26/2023 |
| 20 | 1/27/2023 |
| 20 | 1/28/2023 |
| 20 | 1/29/2023 |
| 20 | 1/30/2023 |
| 20 | 1/31/2023 |
lbendlin Found this on Marc Russo's blog, if anyone needs a solution. This works:
PY Last Day Selection = VAR LastDaySelection = LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds] ) VAR CurrentRange = DATESBETWEEN ( 'Calendar'[Calendar Date], MIN ( 'Calendar'[Calendar Date] ), LastDaySelection ) VAR PreviousRange = SAMEPERIODLASTYEAR ( CurrentRange ) RETURN IF ( LastDaySelection > MIN ( 'Calendar'[Calendar Date]),CALCULATE ( [Gross Adds], PreviousRange ))