Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
I am very new to power bi sir. What will be the base table structure sir for creating this measure?
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.
I am very new to power bi sir. What will be the base table structure sir for creating this measure.
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 😞
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |