How to calculate 4th year sales value

[BhavyaM]

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.

[Icey · Accepted solution]

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.

[BhavyaM]

Thanks your solution was very helpfull and it worked😊

[edhans]

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

[BhavyaM]

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()

[edhans]

@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