Age Calculation with DOB column from Table A and Date column from Table B

0
votes

I am new to Power BI and would like to know how I can calculate the age of a customer based on the DOB from Table A and Date from Table B. Table B is a Date table which is used to select a date period to run the report. So the report runs on Month Year e.g run the report for August 2020 etc

I have tried to use datediff(dob,max(date)),year but i am getting the wrong results. E.g if a customer has birth date 01/08/1991 and i run the report for July 2020, the age should be 28 and not 29.

TIA

1
powerbidaxcalculationmeasuredob
Add sample data from both table and your expected output. – mkRabbani
Report run for July 2020 Sample Data Expected Result Name Measure Name Measure Customer A 29 Customer A 28 Customer B 29 Customer B 28 – Yasir

1 Answers

0
votes

Do not connect those table, Date table is a Parameter table.

Create a measure:

Average Age = 
    VAR _maxDate = MAX('Table B'[Date])
RETURN
   AVERAGEX(
        'Table A',
        INT ( YEARFRAC ( Customer[Birthdate], _maxDate, 1 ) )
   )

source of the code is from sqlbi.com go check the article for more details

Did not tested this out.