How to calculate Last year values

[m1ngle]

Hey all - I have a graph that works great in every way except the legend displays as a date. 

tl;dr - can I display this graph with the year only as the legend without breaking the calculations.

Is there any way I can display JUST the year portion of the date? Here's what I did

The "Period" column that I'm using for the legend is just a year. I made it into a date column as so
Fulldate =  "01/01/"& 'Table'[Period]

I basically just stick an 01/01 on the front and tell Power BI to recognize as a date.

To get Last Years Financial FTE (the height of the column), I first calculate the value of the last period
LY = CALCULATE(SUM('Table'[value]),SAMEPERIODLASTYEAR('Table'[Fulldate]))

I calculate this year's value 
TY = CALCULATE(SUM('Table'[Value])) and subtract the

LYDiff = [TY] - [LY] to get the change.

This is all great. I just need the legend as a year only. When I use [Fulldate].[Year] or whatever, the calculation breaks and I get just the totals and not the difference. I'm attaching a dataset of toy data. I would love to have a power BI I could attach that would show you what I want, but when I try to recreate the calculations with this data I get a circular dependency error even though I'm using the exact same fields.

Vehicle	Brand	Value	Period
Car	Honda	38	2024
Car	Toyota	147	2024
Car	Ford	254	2024
Truck	GMC	80	2024
Truck	Dodge	253	2024
Bike	Schwinn	294	2024
Bike	Huffy	83	2024
Airplane	Cessna	148	2024
Airplane	Boeing	12	2024
Car	Honda	194	2025
Car	Toyota	97	2025
Car	Ford	231	2025
Truck	GMC	83	2025
Truck	Dodge	6	2025
Bike	Schwinn	152	2025
Bike	Huffy	39	2025
Airplane	Cessna	59	2025
Airplane	Boeing	152	2025
Car	Honda	291	2026
Car	Toyota	178	2026
Car	Ford	299	2026
Truck	GMC	167	2026
Truck	Dodge	137	2026
Bike	Schwinn	287	2026
Bike	Huffy	278	2026
Airplane	Cessna	118	2026
Airplane	Boeing	50	2026
Car	Honda	217	2027
Car	Toyota	258	2027
Car	Ford	188	2027
Truck	GMC	119	2027
Truck	Dodge	246	2027
Bike	Schwinn	126	2027
Bike	Huffy	242	2027
Airplane	Cessna	296	2027
Airplane	Boeing	221	2027

[Khushidesai0109]

First of all You've correctly calculated the difference between This Year's value and Last Year's value.

Now, coming to the issue of displaying only the year portion of the date in the legend without breaking the calculations, you can achieve this without using the Fulldate column. Instead, you can directly use the Period column and format it in the way you want.

Here's how you can modify your legend to display only the year:

1. In your visual, instead of using [Fulldate] or [Fulldate].[Year], use [Period] directly.
2. Format the [Period] column in the legend to display only the year portion.

To format the [Period] column:

1. Go to the Data view in Power BI Desktop.
2. Find the [Period] column.
3. Click on the dropdown arrow next to the column name.
4. Choose "Format" and select "Year" from the list of date formats.
   
   

   If this post helps, Please give thumbs up and please consider Accept as the solution to help the other members find it more quickly..
   
   Thank you!!

   
   
   

[DataInsights · Accepted solution]

@m1ngle,

You're on the right track with the Fulldate column. In this example, I created Fulldate in Power Query (custom column; set the data type to Date):

Table.AddColumn(ChangeType, "Fulldate", each #date([Period], 1, 1))

This allows you to use the DAX function CALENDARAUTO to create a date table. There are various ways to create a date table. Here's a simple DAX calculated table. You can add other columns such as Quarter, Month, etc. After you create this table, mark it as a date table (right-click the table and select "Mark as date table").

DateTable = 
ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ) )

Create a relationship between the tables:

Use DateTable[Year] in the legend.

Revise your measure as follows:

LY =
CALCULATE ( SUM ( 'Table'[value] ), SAMEPERIODLASTYEAR ( DateTable[Date] ) )