How to convert dates to Month and Year in Power BI?

by

I have a dataset with employee performance reviews, including periodical HR review dates. Need to create a visual in Power BI that shows the number of reviews conducted each month and year, without showing the specific day. I’m struggling to group the dates correctly and extract just the month and year information and ideas here?

Transform datetimes to year and moth columns

\This tutorial will guide you through the process using an HR dataset, demonstrating how to extract month and year, create a measure for counting reviews, and display results in a meaningful visual.

As we typically do, we will use an imaginary dataset in Power BI, the EmployeeReviews table.
Below you can find the table columns:

  • ReviewID (Whole Number)
  • EmployeeName (Text)
  • ReviewDate (Date)
  • ReviewScore (Decimal Number)

Step-by-step Instructions

  1. Create calculated columns for Month, Year, and MonthYear:
    a. Select the EmployeeReviews table
    b. Click “New Column” in the Modeling tab
    c. Using formula row, define your additional columns using the following DAX formulas:
   Month = FORMAT(EmployeeReviews[ReviewDate], "MMM")

   Year = YEAR(EmployeeReviews[ReviewDate])

   MonthYear = FORMAT(EmployeeReviews[ReviewDate], "MMM") & " " & FORMAT(YEAR(EmployeeReviews[ReviewDate]), "0000")
  1. Next, go ahead to create a measure to count the number of reviews:
    a. Right-click on the EmployeeReviews table
    b. Select “New measure”
    c. Enter the following DAX formula:
   Review Count = COUNTROWS(EmployeeReviews)
  1. Optional: Create a visual:
    a. Select a Matrix visual from the Visualizations pane
    b. Drag the MonthYear column to the Rows field
    c. Drag the Review Count measure to the Values field.
  2. Optional: Add a year slicer:
    a. Add a Slicer visual and use the Year column you created earlier

Few things to remember

  • The DAX FORMAT function converts dates to text in a specified format
  • The DAX function COUNTROWS counts the number of rows in a table, filtered by the current context
  • Using separate columns for Month, Year, and MonthYear provides flexibility for different types of analysis and visualizations
  • Concatenating Month and Year allows for chronological sorting while maintaining readability