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.
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
I tried this: ThisPeriodLY = CALCULATE(IHeads[Total Sales],FILTER(ALL(DateTable[Date]),DateTable[Date]=MAX(DateTable[Year])-1),SAMEPERIODLASTYEAR(DateTable[Date]))
But I get no values
I wish I could just see an example where this has been done before, so I can at least know it is possible.
I gave it a try with my data. I don't have time to dig in and see if it's truly correct, but something here may get you on the right track.
My new measures created for your requirements:
RevenueCurrentYTD = CALCULATE(SalesDataAggregated[Total Revenue], Dates[CalendarYear] = Year(Now()))
RevenueCurrentPeriodLY = CALCULATE(SalesDataAggregated[Total Revenue], FILTER(ALL(Dates), Dates[CalendarYear] = YEAR(NOW())-1), DATESMTD(SAMEPERIODLASTYEAR(LASTDATE(SalesDataAggregated[Date]))))
I was able to create a quick table with my product categories, Revenue YTD (for the current date), and Revenue for the current period last year. Many of the current period last year values were blank because we just didn't have sales for those categories in that month last year. But there were values for the other product categories and they seemed reasonable.
Try it, use what you can from it, and good luck!
Here are the formulas I used for the measures.
Total Revenue = SUM(SalesDataAggregated[Revenue])
Total Revenue This Period LY = CALCULATE(SalesDataAggregated[Total Revenue], FILTER(ALL(Dates), Dates[CalendarYear] = MAX(Dates[CalendarYear])-1), DATESMTD(SAMEPERIODLASTYEAR(LASTDATE(SalesDataAggregated[Date]))))
I cannot fully explain the use of LASTDATE in the formula. I did this several months ago and have had to work on other projects since then. At the time, I was doing a lot of reading and research, and I believe LASTDATE is there so I don't compare an incomplete current month this year to a complete month last year.
Edit to add:
A big question I have looking at your sample output is - what is the period? I'll go back and reread the thread to see if you explain it there.
I'm beginning to understand what you're trying to do - almost.
There is a DAX function Now(). You can use it with Year() to always have the current year for your calculation. Something like
Year(Now())
You may also want to understand DAX row context and filter context. It's pretty complicated, but important.
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
I am trying to your suggestions now, thank you so much! jblackshear