Forum Discussion

jim_hubbardUIHS's avatar
jim_hubbardUIHS
Frequent Visitor
1 year ago
Solved

Calculate age based on a selected date

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
 
 

 

 

Need Help
  • PijushRoy's avatar
    PijushRoy
    1 year ago

    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 

1 Reply

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    1 year ago

    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