How to create discrete year / month measures from a date column for use with slicers in a report?

[Zelbinian]

I have a table that has data from field reports and each report has a date attached to it. The rest of the data in the report is straightforward enough to make visualizations out of, but I can't figure out this part. I'm trying to create a measure for Month and Year, have those be filter buttons on the report, and the rest of the visualizations would filter based on those selections.

Trouble is, I can't figure out how to do this in DAX. Based on the errors I've been getting the YEAR function doesn't seem to want a column name, it just wants text. In the Query Editor I can make additional columns for Month and Year based on the date column but it seems silly to add redundant columns like that when the data can be easily calculated from a column I already have. How can I do this?

[v-yuta-msft]

@Zelbinian,

I have a table that has data from field reports and each report has a date attached to it. The rest of the data in the report is straightforward enough to make visualizations out of, but I can't figure out this part. I'm trying to create a measure for Month and Year, have those be filter buttons on the report, and the rest of the visualizations would filter based on those selections.

Trouble is, I can't figure out how to do this in DAX. Based on the errors I've been getting the YEAR function doesn't seem to want a column name, it just wants text. In the Query Editor I can make additional columns for Month and Year based on the date column but it seems silly to add redundant columns like that when the data can be easily calculated from a column I already have. How can I do this?

---

Could share some sample data and clairfy more details about your requirement and expected result?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zelbinian]

I have a column of dates, in a date format. I wish to use these dates in a slicer to filter the rest of the data in the report by year or month, so I want the slicer to show years and months and not indiv days. My hypothesis is I need to make a measure for YEARS or MONTHS but I'm terrible at DAX and haven't figured out how.

Hopefully that restatement helps because the problem is otherwise pretty straightfoward.

[v-yuta-msft]

@Zelbinian,

If, you can create a new column using DAX function like YEAR() and MONTH() then create a slicer based on that new column.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.