Forum Discussion
SAMEPERIODLASTYEAR WITH YTD
(I'm still pretty new at this myself but I encountered this situation)
I think your previous YTD is using the same context as the current YTD, or maybe you have a date filter on the whole matrix to report a specific year, so it isn't finding any data. Here is the thread with the problem I had and the solution.SAMEPERIODLASTYEAR with a year filter
What I ended up doing was this (this is from my own solution, I haven't tried to fit it to yours)
RevenueLastYTD = CALCULATE([RevenueYTD], FILTER(ALL(Dates), Dates[CalendarYear]=MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))The key is using CALCULATE and FILTER. FILTER(ALL(Dates), ...) first opens up the dates context to use all dates again (because it may be currently set at a specific year, either because of a date filter on the whole matrix, or because of the row it is on). Then the next part of the filter statement sets a new filter for the previous year. I'm not entirely sure if that part is necessary since I'm calling SAMEPERIODLASTYEAR. I first did this several months ago and haven't worked with it much since then.
But take a look at using FILTER to open up the context in your previous year calculation.
Hi Anonymous
I have tried so many variations of this, it has to be related to my date table, in my opinion. I just have not been able to tweak the table. Do you mean add the month and year to the values or the grid, when I do that, it explodes the data, but it does show that the correct months are not being displayed. I would expect to only see data from Jan and Feb of 2016 and 2017..
Hi umpoohg,
Create a Matrix visual as shown in the following screenshot, then filter the years using a slicer based on your needs. But in this sceanrio, we are not able to filter month as SAMEPERIODLASTYEAR only works with contiguous date selections.
Thanks,
Lydia Zhang
Hi Anonymous,
Unfortunately, the requirement needs to be in a grid, per the user. So the matrix will not work, in the way that you have it formatted. I am starting to think that it cannot be done, in the way I want it. To use 'Date Intelligence' tables, I would think I would not need a slicer. If I am requesting Year To Date, the system should know I am talking about this year, until today. I would also think the system would know when i am asking for the sameperiodlastyear, it is looking for 01/01 - 03/01 of 2016, which represent the period we are in now, but last year.
Anyway, thank you for looking.
(I'm still pretty new at this myself but I encountered this situation)
I think your previous YTD is using the same context as the current YTD, or maybe you have a date filter on the whole matrix to report a specific year, so it isn't finding any data. Here is the thread with the problem I had and the solution.SAMEPERIODLASTYEAR with a year filter
What I ended up doing was this (this is from my own solution, I haven't tried to fit it to yours)
RevenueLastYTD = CALCULATE([RevenueYTD], FILTER(ALL(Dates), Dates[CalendarYear]=MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))The key is using CALCULATE and FILTER. FILTER(ALL(Dates), ...) first opens up the dates context to use all dates again (because it may be currently set at a specific year, either because of a date filter on the whole matrix, or because of the row it is on). Then the next part of the filter statement sets a new filter for the previous year. I'm not entirely sure if that part is necessary since I'm calling SAMEPERIODLASTYEAR. I first did this several months ago and haven't worked with it much since then.
But take a look at using FILTER to open up the context in your previous year calculation.
In your dates table what is calendar year, is that just the Year i.e. 2017? I feel like it has to be the date table, I have seen your post earlier, but that is not really working either. I am not getting a message that I do not have all the dates in my date table. However, I am pretty sure I have more dates in my date table than I do in the header(facts)table.
Thank you so much
Hi Anonymous,
Unfortunately, the requirement needs to be in a grid, per the user. So the matrix will not work, in the way that you have it formatted. I am starting to think that it cannot be done, in the way I want it. To use 'Date Intelligence' tables, I would think I would not need a slicer. If I am requesting Year To Date, the system should know I am talking about this year, until today. I would also think the system would know when i am asking for the sameperiodlastyear, it is looking for 01/01 - 03/01 of 2016, which represent the period we are in now, but last year.
Anyway, thank you for looking.