cancel
Showing results for
Did you mean:
Helper III

## Showing last three year data on visual

Hi friends,

I have a slicer with years in it (2015, 2016, 2017, 2018, 2019, 2020, 2021). I have three card visuals showing some information for three years respectively. The manager wants the information on these cards to get changed to the latest three years of the year slected on slicer. So if the slicer has value selected as 2021, the three card visuals will have data of 2021, 2020 & 2019. If the slicer has value selected as 2019, the three card visuals will have data of 2019, 2018 & 2017.

Pls help on how o manage this.

5 ACCEPTED SOLUTIONS
Solution Sage

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

Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

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

Solution Sage

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

Solution Sage

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

12 REPLIES 12
Helper III

I am very new to power bi sir. What will be the base table structure sir for creating this measure?

Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

I am very new to power bi sir. What will be the base table structure sir for creating this measure.

Solution Sage

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

Helper III

WHat is calendar in the DAX shared by you? Can yuo pls share the base table format so that it will give me idea sir. Thanks a lot

Solution Sage

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

Helper III

Thanks sir. So I have data as below in table named as 'Salesdata'. So I need to mention 'Salesdata' instead of 'Calendar' word in the formula? Also, ahead of calendar, yuo mentioned it as Date, Should I keep it as Date only or it will get changed to 'Year' in my case??

Year   Sales

2021   1111

2020   2222

2019   3333

2018   4444

Solution Sage

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

Helper III

Thanks a lot sir. This worked for the number field of sales amount. There is another information that also I want to show in card visual, smilar to sales amount.

So when I select year 2021, the card visual should show the Status of the previous year (2020) as Bad. I tried using the same formula given by you but that is not working on text values

Year   Sales  Status

2021   1111  Good

2018   4444   Better

Solution Sage

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

Helper III

This worked. Thanks a lot sir

Helper III

Hi Sir, Can u plsssss help 😞

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors