Tuesday, August 10, 2021

Power BI: How to create Age slicer if date of birth is the only field available in the data model

If there is a requirement of creating an Age slicer which is numeric slicer and only date of birth is available in data model then we can achieve by getting fractional year value between date of Birth & Current date using YEARFRAC DAX function.

YEARFRAC function: Calculates the fraction of the year represented by the number of whole days between two dates.

YEARFRAC(<start_date>, <end_date>, <basis>)  

Note: <basis> is optional

Create a new column named "Age" with below formula

Table Name – Employee
Column Name – DateofBirth 

Age = INT(YEARFRAC(Employee[DateofBirth]),TODAY(),1))

Create a new slicer for "Age" with newly created column "Age".