cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harshadrokade
Helper III
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
HashamNiaz
Solution Sage
Solution Sage

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

View solution in original post

Ashish_Mathur
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

View solution in original post

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

View solution in original post

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

View solution in original post

12 REPLIES 12
harshadrokade
Helper III
Helper III

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

Ashish_Mathur
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

HashamNiaz
Solution Sage
Solution Sage

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

View solution in original post

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

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

View solution in original post

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

 

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

View solution in original post

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

2020   2222   Bad

2019   3333   Bad

2018   4444   Better

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

View solution in original post

This worked. Thanks a lot sir

Hi Sir, Can u plsssss help 😞

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

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