Forum Discussion
SAMEPERIODLASTYEAR with a year filter
- 9 years ago
I wish I could accept both answers as the solution, because I appreciate your help in pointing me in the right direction.
I had tried many different variations on your suggestions, with close-but-not-quite-right results.
I thought that maybe the date intelligence functions were not working quite right with my date table, and decided to rebuild it. The dates in my data warehouse are integers of the form 20161121. Originally, I brought them in as is, then created a calculated column in Power BI to create the date field using the date parts (year, month, day stored in separate fields). Then I matched the tables up in the Power BI designer.
This time I created the calculated date key in the SQL query, for both tables. Now when I use the normal date functions, they work as expected. It definitely helped to know the syntax that is supposed to work, so again I thank you both.
What I ended up with for my RevenueLastYTD measure is:
RevenueLastYTD = CALCULATE([RevenueYTD], FILTER(ALL(Dates), Dates[CalendarYear]=MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))
It correctly displays, for each month, the YTD for the same month of the previous year.
I wish I could accept both answers as the solution, because I appreciate your help in pointing me in the right direction.
I had tried many different variations on your suggestions, with close-but-not-quite-right results.
I thought that maybe the date intelligence functions were not working quite right with my date table, and decided to rebuild it. The dates in my data warehouse are integers of the form 20161121. Originally, I brought them in as is, then created a calculated column in Power BI to create the date field using the date parts (year, month, day stored in separate fields). Then I matched the tables up in the Power BI designer.
This time I created the calculated date key in the SQL query, for both tables. Now when I use the normal date functions, they work as expected. It definitely helped to know the syntax that is supposed to work, so again I thank you both.
What I ended up with for my RevenueLastYTD measure is:
RevenueLastYTD = CALCULATE([RevenueYTD], FILTER(ALL(Dates), Dates[CalendarYear]=MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))
It correctly displays, for each month, the YTD for the same month of the previous year.
I couldn't get this work, but doing it this way resolved the issue.