How do I shorten date to display just the year?

[powerbi-learner]

Hi Power BI experts.

I have a card that displays the latest year from my dataset.

At present it's showing the longform date e.g. Wednesday 14 December 2017.

Here it is under the spotlight:

I'd like the card to just show the year.

How can I make the card just show the year i.e. the card should just say"2016".

Thanks.

Here's my source data:

Do I need to change the date format in the source data?

Or is there some adjustment I can make on the card visual?

Thanks experts!!

[dilumd · Accepted solution]

Hi @powerbi-learner

Best thing would be to use a calendar table separately and relate this table to that calendar table. And then use the year in that calendar table.

Below code can be used to populate a calendar in query editor.

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
in
CreateDateTable

If this is not an option for you, you can add below calculated column.

Year Column = YEAR('Your Table Name'[Date])

Hope this helps.

[powerbi-learner]

@dilumdThanks for your reply.

I clicked Create Column and entered your formula.

Thanks so much!!!

🙂