topic Re: Same Period Last Year with Filter in DAX Commands and Tips

Same Period Last Year with Filter

BrianNeedsHelp — Thu, 31 Oct 2024 17:47:52 GMT

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

Re: Same Period Last Year with Filter

lbendlin — Thu, 31 Oct 2024 20:37:37 GMT

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

Re: Same Period Last Year with Filter

BrianNeedsHelp — Thu, 31 Oct 2024 21:00:14 GMT

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".

Re: Same Period Last Year with Filter

lbendlin — Thu, 31 Oct 2024 21:06:06 GMT

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

Re: Same Period Last Year with Filter

BrianNeedsHelp — Thu, 31 Oct 2024 21:11:05 GMT

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

Re: Same Period Last Year with Filter

BrianNeedsHelp — Thu, 31 Oct 2024 21:20:02 GMT

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?

Re: Same Period Last Year with Filter

lbendlin — Thu, 31 Oct 2024 21:39:43 GMT

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-Forum/ba-p/963216

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/1447523

Re: Same Period Last Year with Filter

BrianNeedsHelp — Thu, 31 Oct 2024 23:29:40 GMT

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.

Re: Same Period Last Year with Filter

lbendlin — Thu, 31 Oct 2024 23:45:28 GMT

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

Re: Same Period Last Year with Filter

BrianNeedsHelp — Fri, 01 Nov 2024 00:08:15 GMT

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

Re: Same Period Last Year with Filter

BrianNeedsHelp — Fri, 01 Nov 2024 01:25:23 GMT

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