Forum Discussion

MK97's avatar
MK97
Frequent Visitor
4 years ago
Solved

Upcoming anniversaries

Hello,

 

I have a problem finding an employee upcoming anniversaries. 

 

I need to create two dax measures:

  1. Anniversaries in 1 month (I need to find anniversaries from today to today+30 days)
  2. Anniversaries in 3 months (I need to find anniversaries from today to today+90 days)

Data example:

Employees   Date of Birth   Age

Employee1   2000-01-13     22

Employee2   1998-06-15     23

Employee3   1997-02-10     24

Employee4   1992-03-02     29

Employee5   1997-02-15     24

 

Anniversaries in 1 month (Expected result):

Employees   Date of Birth   Age

Employee3   1997-02-10     25

 

Anniversaries in 3 month (Expected result):

Employees   Date of Birth   Age

Employee4   1992-03-02     30

Employee5   1997-02-15     25

DAX
DAX Calculation
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    Hi MK97 

    It looks like Samarth's solution wouldn't handle year end (ie today is less than 3 months until end of the year).

    Here's a version to handle that

    Anniversary in 1 to 3 Months = 
VAR _Today = TODAY()
VAR _DateOfBirth = SELECTEDVALUE(Employee[Date of Birth])
VAR _OneMonthFrac = 1 / 12
VAR _Diff = 1 - MOD(YEARFRAC(_DateOfBirth, _Today), 1) // Percent of Year until next birthday
VAR _Result = 
IF(
    (_OneMonthFrac * 1) < _Diff        // between 1 month..
    && _Diff <= (_OneMonthFrac * 3),    // and 3 months until next birthday
    "True"
)
RETURN
	_Result

    and a 1 month one

    Anniversary in 1 Month = 
VAR _Today = TODAY()
VAR _DateOfBirth = SELECTEDVALUE(Employee[Date of Birth])
VAR _OneMonthFrac = 1 / 12
VAR _Diff = 1 - MOD(YEARFRAC(_DateOfBirth, _Today), 1) // Percent of Year until next birthday
VAR _Result = 
IF(
    _Diff <= (_OneMonthFrac * 1),   
    "True"
)
RETURN
	_Result

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi MK97 

     

    You can create below two measure and use it as filter on your visual:-

    _3MonthFilter = 
IF (
    DATE ( YEAR ( TODAY () ), MONTH ( MAX ( Employee[Date of Birth] ) ), DAY ( MAX ( Employee[Date of Birth] ) ) )
        >= TODAY () + 30
        && DATE ( YEAR ( TODAY () ), MONTH ( MAX ( Employee[Date of Birth] ) ), DAY ( MAX ( Employee[Date of Birth] ) ) )
            <= TODAY () + 90,
    1,
    0
)
    _1MonthFilter = 
IF (
    DATE ( YEAR ( TODAY () ), MONTH ( MAX ( Employee[Date of Birth] ) ), DAY ( MAX ( Employee[Date of Birth] ) ) )
        <= TODAY () + 30,
    1,
    0
)

     

    Output:-

     

     

     

    Thanks,

    Samarth

     

    • PaulOlding's avatar
      PaulOlding
      Solution Sage
      4 years ago

      Hi MK97 

      It looks like Samarth's solution wouldn't handle year end (ie today is less than 3 months until end of the year).

      Here's a version to handle that

      Anniversary in 1 to 3 Months = 
VAR _Today = TODAY()
VAR _DateOfBirth = SELECTEDVALUE(Employee[Date of Birth])
VAR _OneMonthFrac = 1 / 12
VAR _Diff = 1 - MOD(YEARFRAC(_DateOfBirth, _Today), 1) // Percent of Year until next birthday
VAR _Result = 
IF(
    (_OneMonthFrac * 1) < _Diff        // between 1 month..
    && _Diff <= (_OneMonthFrac * 3),    // and 3 months until next birthday
    "True"
)
RETURN
	_Result

      and a 1 month one

      Anniversary in 1 Month = 
VAR _Today = TODAY()
VAR _DateOfBirth = SELECTEDVALUE(Employee[Date of Birth])
VAR _OneMonthFrac = 1 / 12
VAR _Diff = 1 - MOD(YEARFRAC(_DateOfBirth, _Today), 1) // Percent of Year until next birthday
VAR _Result = 
IF(
    _Diff <= (_OneMonthFrac * 1),   
    "True"
)
RETURN
	_Result