cancel
Showing results for
Did you mean:
frodriguezw Frequent Visitor

## Sum of Year and Previous Year Sales

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!

3 REPLIES 3
Highlighted
frodriguezw Frequent Visitor

## Sum of Current and Previous Year Sales

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! Super User IV

## Re: Sum of Current and Previous Year Sales

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

themistoklis New Contributor

## Re: Sum of Year and Previous Year Sales

@frodriguezw

Can you try the following 2 DAX formulas and see if they work on your case:

YTD This year

```NetValue_YTD_TY =
IF (
MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( 'Master Table'[Reference_Date]), ALL ( 'Master Table' ) ),
CALCULATE ( SUM('Master Table'[Net_Value]), DATESYTD ( 'Calendar'[Date] ) )
)```

YTD Last Year

```NetValue_YTD_PY = VAR DataMaxDate =
CALCULATE ( MAX ( 'Master Table'[Reference_Date] ), ALL ( 'Master Table' ) )
RETURN
CALCULATE (
[NetValue],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( 'Calendar'[Date] ),
DATESBETWEEN ( 'Calendar'[Date], BLANK (), DataMaxDate )
)
)
)```

You can notice that i use a Calendar and get the MAX date from the table im working on. This means that even the last day of the Calendar is on 31st of December 2019 this wont affect any calculations.

Announcements #### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system. #### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge. #### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests! #### Power Platform Online Conference 