Showing last three year data on visual

Hi harshadrokade !

You can create 2 additional measure to show KPI's for Last 2 years. You can use following DAX to create your measure;

_Sales = SUM(Table[Sales])

_Last1YearSales = CALCULATE([_Sales], DATEADD('Calendar'[Date], -1, YEAR))

_Last2YearSales = CALCULATE([_Sales], DATEADD('Calendar'[Date], -2, YEAR))

You can replace Table name in your first measure, preceeding 2 measures will use the first measure & calculate Last 1 & Last 2 Year sales.

Regards,

Hasham

Hi,

So if you select 2020 and your measure is Sales then write these measures and place them in the other 2 card visuals:

LY sales = calculate([sales],previousyear(calendar[date]))

Sales 2 years ago = calculate([LY sales],previousyear(calendar[date]))

Hope this helps.

Hi harshadrokade !

Calendar is a dimension table from where you pulling all the dates. This will be the table from your Model where you have picked your Slicer Year.

Regards,

Hasham

Hi harshadrokade !

Please try creating a calendar dimension table using following DAX;

Calendar = CALENDAR(DATE(2015,01,01), DATE(2025,12,31))

Now create an active relationship between Calendar dimension & your SalesData table based on Date column.

After that you can use the DAX formulas i mentioned;

_Sales = SUM(SalesData[Sales])

_Last1YearSales = CALCULATE([_Sales], DATEADD('Calendar'[Date], -1, YEAR))

_Last2YearSales = CALCULATE([_Sales], DATEADD('Calendar'[Date], -2, YEAR))

Please these measure on each card separately. 

Regards,

Hasham

Hi harshadrokade !

Do you have multiple status for single year, or you only have 1 status per year. You can use something like below;

_Status = MAX(SalesData[Status])

_Last1YearSales = CALCULATE([_Status], DATEADD('Calendar'[Date], -1, YEAR))

_Last2YearSales = CALCULATE([_Status], DATEADD('Calendar'[Date], -2, YEAR))

All the other parts remains the same.

Regards,

Hasham