Hi!
I have the following problem and need some help please!
I have a Sales Table, which is connected to a Calendar Table. Sales Table date biggest value is at most the current date value.
Calendar Table, for some reasons not worth mentioning, is formated to be since January 1st 2013 by the end of next year (I'm writing this on 2018, so the calendar date last value will be December 31st 2019).
I have a graph showing the evolution of sales for the last 3 years. As X-axis, I'm using month and year of the Date Calendar and as values I'm using [Total Sales] = SUM(Sales[Amount]) measure. So far so good.
I want to create a measure to use them as KPI that sums the current year value and previous year value. So I created the following measure:
CurrentYear Sales =
VAR
selectedYear = YEAR(MIN(MAX(Calendar Table[Date]),[CurrentDate]))
RETURN
CALCULATE([Total Sales], FILTER(Calendar Table, Calendar Table[Year] = selectedYear))
I am calculating selectedYear because if I want to make clic on the Sales Evolution Graph in a specific month, I want this number to give me the current Month Total Sales. So if I select January, I want CurrentYear Sales to give me the total of January Sales, but if I don't have anything selected, then CurrentYear Sales is going to be 0 because Calendar Date[Date] will be December 31st 2019. Nevertheless, this measure is O.K.
The problem is with the previous year sales. I can't use SAMEPERIODLASTYEAR on the date field of Calendar Date, because it will return the current year when I am not filtering anything. I created the following measure as a "temporary solution":
PreviousYear Sales =
IF(MAX(Calendar Table[Date]) > DATE(YEAR([CurrentDate])+1 ,MONTH([CurrentDate]) , DAY([CurrentDate])),
VAR
selectedDate = MAX(Calendar Table[Date])
VAR
selectedYear = YEAR(selectedDate) - 2
RETURN
CALCULATE([Total Sales],
FILTER(
ALL(Calendar Dates),
Calendar Dates[Year] = selectedYear
)
),
CALCULATE([Total Sales],
SAMEPERIODLASTYEAR(Calendar Date[Date])
)
)Doing this I am saying, If is not December 31st, then the current date is always going to be less than the maximum value of my Calendar Table, so I want it to calculate the total value of the previous 2 years (which is the previous current year). I'm going to have a problem on December 31st and that is what I need to fix if someone has a clue over what to do here.
Also, I have problems if I put month slicers, because by selecting a Month, the current date day and month on the next year
DATE(YEAR([CurrentDate])+1 ,MONTH([CurrentDate]) , DAY([CurrentDate]))
is sometimes going to be bigger than the date selected in Date Table. And also, the number returned I actually have no idea what it is measuring (instead of using SAMEPERIODLASTYEAR I can use FILTER, select ALL(Date Table) and filter by the specific Year and Month to solve this, but nevertheless, when filtering by month I will get the full year value instead of last year value).
So I really don't know what else to do. Some help would be really appreciated.
Thanks!
