cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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!

Highlighted
Super User IV
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...


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

themistoklis Memorable Member
Memorable Member

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.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors