cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

Re: 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?


 

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.

Highlighted
Frequent Visitor

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

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.

Highlighted
Community Support
Community Support

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

@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

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors