Calculate age based on a selected date

[jim_hubbardUIHS]

Hi All, 

Thanks in advance for any help. 

I am working on a project where we are trying to calculate the age of a person (Calculated colum) based on a selected date. Inother words, how old is this person at the end of a selected QTR. 

I can make all the measures display the correct date as I select a QTR, but the calculated colum in the Fact_Person table doesn't update. It stays on the "alternative date". My assumtion is the calculated column is NOT aware of the selection change. 

Here is the measure (basically)

Start of Seleted QTR = SELECTEDVALUE('Dim_Dates QTRS'[Date],"1/1/2024")

End Current of quarter = EOMONTH ( [Start of Seleted QTR], 2 )

Here is the column

Age In Months End of Current QTR = DATEDIFF(Fact_Patient[dob],[End Current of quarter],MONTH)

Thanks for any insights

jim

[PijushRoy · Accepted solution]

Hi @jim_hubbardUIHS 
Calculated column never aware about the selected QTR in visual.
You should create a measure, then you can show the Patient wise aga in visual

Calculate Age = 
VAR SelectedQuarterStartDate = 
    CALCULATE(
        MIN('Calendar'[Date]),
        FILTER(
            'Calendar',
            'Calendar'[Quarter] = SELECTEDVALUE('Calendar'[Quarter]) &&
            'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year])
        )
    )
VAR CurrentDate = 
    IF(ISBLANK(SelectedQuarterStartDate), TODAY(), SelectedQuarterStartDate)
RETURN 
    DATEDIFF(MIN('YourTable'[DateOfBirth]), CurrentDate, MONTH)

Sample Measure code below