cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
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. 

 

Please help me how to find 4th year sales value.

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
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
    )
)

4th.PNG

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.

View solution in original post

5 REPLIES 5
Highlighted
Super User II
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 get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
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
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
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 get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
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
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
    )
)

4th.PNG

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.

View solution in original post

Highlighted
Helper V
Helper V

Re: How to calculate 4th year sales value

Thanks your solution was very helpfull and it worked😊

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

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!

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