Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
harshadrokade
Post Partisan
Post Partisan

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
Super User

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
Post Partisan
Post Partisan

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

Ashish_Mathur
Super User
Super User

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/

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

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

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

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

This worked. Thanks a lot sir

Hi Sir, Can u plsssss help 😞

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.