Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i have a Sales table that has dates in a column, 4/15/2015, etc. I just wanted to show the Years. I thought I could just make a new measure and use a formula similiar to this...
years = YEAR('Sales Table' [dates])
i dont even get the table/column names with Intellisense... why cant I do something like this in PBI? Whats the proper way? a short explanation would be appreciated.
Solved! Go to Solution.
Hi @ilcaa72,
YEAR function expect a single date in datetime or text format, containing the year you want to find.
You can get:
YEAR(LASTDATE('Sales Table' [dates]))
or
YEAR(FIRSTDATE(Calendar[Date]))
To obtain the list of years you can mark the field as Date Hierarchy and select only the years
Regards.
Hey,
if you create a calculated column in your sales table you can use the function you already have. The benefit of creating a calculated column over a measure is that you can use the calculated column as a slicer. The price you pay, is an additional memory footprint, depending on the number of rows in your sales table.
I would recomment that you consider to create a separate date table and relate this table with your sales table. Here you will find some date related calculations that heavily rely on the usage of a separate date table: https://www.daxpatterns.com/time-patterns/
By the way, intellisense recognizes that the context expects an aggregate function, for this reason it does suggest a tablename. This behavior often reminds me that I should check my syntax and the context 🙂
Hope this what you are looking for
Regards
Tom
Hey,
if you create a calculated column in your sales table you can use the function you already have. The benefit of creating a calculated column over a measure is that you can use the calculated column as a slicer. The price you pay, is an additional memory footprint, depending on the number of rows in your sales table.
I would recomment that you consider to create a separate date table and relate this table with your sales table. Here you will find some date related calculations that heavily rely on the usage of a separate date table: https://www.daxpatterns.com/time-patterns/
By the way, intellisense recognizes that the context expects an aggregate function, for this reason it does suggest a tablename. This behavior often reminds me that I should check my syntax and the context 🙂
Hope this what you are looking for
Regards
Tom
Hi @ilcaa72,
YEAR function expect a single date in datetime or text format, containing the year you want to find.
You can get:
YEAR(LASTDATE('Sales Table' [dates]))
or
YEAR(FIRSTDATE(Calendar[Date]))
To obtain the list of years you can mark the field as Date Hierarchy and select only the years
Regards.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |