Forum Discussion
SAMEPERIODLASTYEAR with a year filter
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.
if you a introducing a filter then you will need to clear the date context in your calcution and then filter for the previous period.
an example would be something like,
calc=calculate(expression, filter(all(dates), date[year] = max(date[year])-1), that is if you have a date dimension with a year column.
If you are trying to get the same month and you have a monthcolum like 201611, you could use the same pattern like calc=calculate(expression, filter(all(dates), date[year] = max(date[monthcolumn])-100).
This Pattern can be used for all prior period calc.
Hope that helps.
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.
glad that you got it all sorted and i could help
I couldn't get this work, but doing it this way resolved the issue.
CALCULATE([Measure], FILTER(ALL(Dates), Dates[Year] = SAMEPERIODLASTYEAR(Dates[Date])))
