Same Period Last Year with Filter

[BrianNeedsHelp]

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 January through October, so I'm trying to filter using MAX Calendar Date.   This below is not working and is computing the results of this year and not last year.  

GA LY = CALCULATE([Gross Adds],FILTER('Calendar','Calendar'[CalendarDate]=MAX([Calendar Date],SAMEPERIODLASTYEAR('Calendar'[Calendar Date]))))

[lbendlin · Accepted solution]

GA LY = var m = max('Calendar'[CalendarDate]) return CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date]),'Calendar'[Calendar Date]<=EDATE(m,-12))

[BrianNeedsHelp]

Hi @lbendlin I thought it was working, but it only works if I filter the months in a slicer.  Otherwise it still counts the whole year.  I've run into this problem on several ideas I've tried.  Do you know how I can get it to just calculate based on Max date of this current year?  

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[BrianNeedsHelp]

I created a sample PBIX with two columns -Date and Gross Adds, and it behaves the same way when I try the formula.  It will sum the whole year instead of filtering it.  But I don't see a way to attach it.  

[lbendlin]

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[BrianNeedsHelp]

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

[BrianNeedsHelp · Accepted solution]

@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 ))

[BrianNeedsHelp]

YES!   After hours of trying differen things.  This is amazing.  THANK YOU! 

[lbendlin]

GA LY = var m = max('Calendar'[CalendarDate]) return CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date]),MONTH(MAX('Calendar'[Calendar Date]))<=MONTH(m))

[BrianNeedsHelp]

@lbendlin  Hi-I'm getting "The true/false expression does not specify a column. Each true/false expression used as a table filter expression must refer to exactly one column".  

[lbendlin · Accepted solution]

GA LY = var m = max('Calendar'[CalendarDate]) return CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date]),'Calendar'[Calendar Date]<=EDATE(m,-12))