cancel
Showing results for
Did you mean:
Highlighted
Helper V

## How to calculate 4th year sales value

I have year column from 2015,2016,2017,2018,2019 and Sales and Product columns

Here I need to achive change % for products  like (currentyear sales/ 4year sales) dynamically.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: How to calculate 4th year sales value

Hi @BhavyaM ,

I create a simple example. Please check:

``Current Year Sales = SUM('Table'[Sales])``
``````4th Year Sales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] IN VALUES ( 'Table'[Product] )
&& 'Table'[Year]
= MAX ( 'Table'[Year] ) - 4
)
)
``````

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Highlighted
Super User II

## Re: How to calculate 4th year sales value

EDIT: Just noticed you have columns of sales for years. You need to unpivot the sales table so the dates are in one column, not multiple columns first. That is normalizing the data, which DAX needs.

The TOPN() function would do this. See the syntax here. I am not sure if you want the last 4 years of data, or 4 years ago. If 4 years, just use TOPN() as is. If you need 4 years ago, you could use the MINX() function on the data returned by TOPN on the date.

Alternatively you could filter your data by the Date table with something like this:

``````Sales 4 Years Ago =
VAR CurrentYear =
YEAR(
TODAY()
)
VAR Sales4YearsAgo =
SUMX(
FILTER(
Sales,
RELATED( Date[Year] ) = CurrentYear - 4
),
Sales[Sales Total]
)
RETURN
Sales4YearsAgo
``````

If you want further help, you'd need to provide some data to assist.

How to provide sample data in the Power BI Forum

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper V

## Re: How to calculate 4th year sales value

I need formula for

Current year sales(2019 sales)  = ?

4th year sales (2015 sales) = ?

Can I apply your formula on my column directly without doing TopN()

Highlighted
Super User II

## Re: How to calculate 4th year sales value

@BhavyaM again, I'll need data. The measure I gave will give you sales for the 4th year, but if you are unable to apply that measure to your data to obtain the sales 4 years ago, then I am not understanding your data structure. See links below.

How to provide sample data in the Power BI Forum

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Community Support

## Re: How to calculate 4th year sales value

Hi @BhavyaM ,

I create a simple example. Please check:

``Current Year Sales = SUM('Table'[Sales])``
``````4th Year Sales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] IN VALUES ( 'Table'[Product] )
&& 'Table'[Year]
= MAX ( 'Table'[Year] ) - 4
)
)
``````

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper V

## Re: How to calculate 4th year sales value

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors