cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zelbinian
Frequent Visitor

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

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?

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@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.

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.

@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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors