cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
frodriguezw Frequent Visitor
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
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
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

themistoklis New Contributor
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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 286 members 3,054 guests
Please welcome our newest community members: