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.
I appreciate your response, thank you.
I tried out the measures as you describe, which are much like my first attempt shown above.
RevenueYTDv2 = CALCULATE([TotalRevenue], DATESYTD(Dates[CalcDate].[Date]))
RevenueLastYTDv5 = CALCULATE([RevenueYTDv2], SAMEPERIODLASTYEAR(Dates[CalcDate].[Date]))
I get the same results as with my previous attempt - when I add the year slicer, the previous YTD values disappear.
Have you tried removing the .[DATE] from Dates[CalcDate].[Date]?
I do not yet understand what the date column does with and without the [date], [year], etc. bits added on.
But when I leave off [date], instead of getting year-to-date total I get only the total for that month.
I end up trying everything with both variations to see if one works and the other doesn't. I then use the least specific one that works. Not very precise or technical, I know. I hope some good understanding comes soon because I don't like working this way.
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.
