Forum Discussion
Resolver ISame 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 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]))))
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 ))
10 Replies
- lbendlin
Super User
GA LY = var m = max('Calendar'[CalendarDate]) return CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date]),MONTH(MAX('Calendar'[Calendar Date]))<=MONTH(m))
BrianNeedsHelplbendlin 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
GA LY = var m = max('Calendar'[CalendarDate]) return CALCULATE([Gross Adds],SAMEPERIODLASTYEAR('Calendar'[Calendar Date]),'Calendar'[Calendar Date]<=EDATE(m,-12))
- BrianNeedsHelp
YES! After hours of trying differen things. This is amazing. THANK YOU!
- 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-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- 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.