Forum Discussion
Solved
Dynamic for Finding next Work anniversary datess
Hai Guys, I want to return the next 3 month's work anniversary celebrants, I Got them but there is one escalation ie this is giving the next 3 months' data, it is not returning the current mont...
I would create a new column
Next anniversary = var joinDate = 'Table'[Join date] var currentYearAnniversary = DATE( YEAR(TODAY()), MONTH(joinDate), DAY(joinDate)) return IF( currentYearAnniversary < TODAY(), DATE( YEAR(TODAY()) + 1, MONTH(joinDate), DAY(joinDate)), currentYearAnniversary )You could then use relatative date filtering on your table visual but that might not fit with your use case. You could create a measure to use as a filter. If you wanted to show anniversaries in the current month and 3 months after you could use
Show anniversary = IF( SELECTEDVALUE( 'Table'[Next anniversary]) <= EOMONTH(TODAY(),4), 1, 0)
I would create a new column
Next anniversary =
var joinDate = 'Table'[Join date]
var currentYearAnniversary = DATE( YEAR(TODAY()), MONTH(joinDate), DAY(joinDate))
return IF( currentYearAnniversary < TODAY(),
DATE( YEAR(TODAY()) + 1, MONTH(joinDate), DAY(joinDate)),
currentYearAnniversary
)You could then use relatative date filtering on your table visual but that might not fit with your use case. You could create a measure to use as a filter. If you wanted to show anniversaries in the current month and 3 months after you could use
Show anniversary =
IF( SELECTEDVALUE( 'Table'[Next anniversary]) <= EOMONTH(TODAY(),4), 1, 0)