cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## List Alerts for Upcoming Milestones in Chart

I am hoping to create a list of individuals who are about to reach specific milestones within our company. For example, a month before they have reached their 5 years at the company, I would like them to be added to a list and then removed from the list once their milestone has hit.

 PErson Date Started Milestone Days until Bob 9/20/2015 5 years 10 Cathy 9/20/2018 10 years 500+ days (would not show up) Billy 9/20/2010 10 years 10 days

If this needs to be broken up into different columns for each milestone so be it, so the chart might look like:

 Person 5 Years Ten Years Bob yes no Billy no yes

And Cathy would only appear when her 10 year milestone is up. Please let me know if this is doable

1 ACCEPTED SOLUTION
Community Support

Hello @bw70316

Yes, this error is because it has a blank value for the [Start Date] column, so simply adjust the formula by the IF column as below screenshot shown:

Column =
IF([Date Started ]=BLANK(),BLANK(),

VAR __Today = TODAY()
VAR __Start = [Date Started ]
VAR __Table =
{
("5 Years",(DATE(YEAR(__Start)+5,MONTH(__Start),DAY(__Start))-__Today)*1.),
("10 Years",(DATE(YEAR(__Start)+10,MONTH(__Start),DAY(__Start))-__Today)*1.),
("15 Years",(DATE(YEAR(__Start)+15,MONTH(__Start),DAY(__Start))-__Today)*1.),
("20 Years",(DATE(YEAR(__Start)+20,MONTH(__Start),DAY(__Start))-__Today)*1.)
}
VAR __DaysUntil = MINX(FILTER(__Table,[Value2]>0),[Value2])
RETURN
__DaysUntil)
Column 2 =
IF([Date Started ]=BLANK(),BLANK(),

VAR __Today = TODAY()
VAR __Start = [Date Started ]
VAR __Table =
{
("5 Years",(DATE(YEAR(__Start)+5,MONTH(__Start),DAY(__Start))-__Today)*1.),
("10 Years",(DATE(YEAR(__Start)+10,MONTH(__Start),DAY(__Start))-__Today)*1.),
("15 Years",(DATE(YEAR(__Start)+15,MONTH(__Start),DAY(__Start))-__Today)*1.),
("20 Years",(DATE(YEAR(__Start)+20,MONTH(__Start),DAY(__Start))-__Today)*1.)
}
VAR __DaysUntil = MINX(FILTER(__Table,[Value2]>0),[Value2])
VAR __Anniversary = MAXX(FILTER(__Table,[Value2]=__DaysUntil),[Value1])
RETURN
__Anniversary)

Best regards

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User

@bw70316 - I did this in 2 columns like below. PBIX attached under sig. You want Table, Column and Column 2. @ me if this is not what you are looking for.

Column =
VAR __Today = TODAY()
VAR __Start = [Date Started ]
VAR __Table =
{
("5 Years",(DATE(YEAR(__Start)+5,MONTH(__Start),DAY(__Start))-__Today)*1.),
("10 Years",(DATE(YEAR(__Start)+10,MONTH(__Start),DAY(__Start))-__Today)*1.),
("15 Years",(DATE(YEAR(__Start)+15,MONTH(__Start),DAY(__Start))-__Today)*1.),
("20 Years",(DATE(YEAR(__Start)+20,MONTH(__Start),DAY(__Start))-__Today)*1.)
}
VAR __DaysUntil = MINX(FILTER(__Table,[Value2]>0),[Value2])
RETURN
__DaysUntil

Column 2 =
VAR __Today = TODAY()
VAR __Start = [Date Started ]
VAR __Table =
{
("5 Years",(DATE(YEAR(__Start)+5,MONTH(__Start),DAY(__Start))-__Today)*1.),
("10 Years",(DATE(YEAR(__Start)+10,MONTH(__Start),DAY(__Start))-__Today)*1.),
("15 Years",(DATE(YEAR(__Start)+15,MONTH(__Start),DAY(__Start))-__Today)*1.),
("20 Years",(DATE(YEAR(__Start)+20,MONTH(__Start),DAY(__Start))-__Today)*1.)
}
VAR __DaysUntil = MINX(FILTER(__Table,[Value2]>0),[Value2])
VAR __Anniversary = MAXX(FILTER(__Table,[Value2]=__DaysUntil),[Value1])
RETURN
__Anniversary

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper V

I was getting a "An argument of function 'DATE' has the wrong data type or the result is too large or too small." That was the result of having blanks in my data. So I learned two things. I very much appreciate this post. Jedi Dax Master.

Community Support

Hello @bw70316

Yes, this error is because it has a blank value for the [Start Date] column, so simply adjust the formula by the IF column as below screenshot shown:

Column =
IF([Date Started ]=BLANK(),BLANK(),

VAR __Today = TODAY()
VAR __Start = [Date Started ]
VAR __Table =
{
("5 Years",(DATE(YEAR(__Start)+5,MONTH(__Start),DAY(__Start))-__Today)*1.),
("10 Years",(DATE(YEAR(__Start)+10,MONTH(__Start),DAY(__Start))-__Today)*1.),
("15 Years",(DATE(YEAR(__Start)+15,MONTH(__Start),DAY(__Start))-__Today)*1.),
("20 Years",(DATE(YEAR(__Start)+20,MONTH(__Start),DAY(__Start))-__Today)*1.)
}
VAR __DaysUntil = MINX(FILTER(__Table,[Value2]>0),[Value2])
RETURN
__DaysUntil)
Column 2 =
IF([Date Started ]=BLANK(),BLANK(),

VAR __Today = TODAY()
VAR __Start = [Date Started ]
VAR __Table =
{
("5 Years",(DATE(YEAR(__Start)+5,MONTH(__Start),DAY(__Start))-__Today)*1.),
("10 Years",(DATE(YEAR(__Start)+10,MONTH(__Start),DAY(__Start))-__Today)*1.),
("15 Years",(DATE(YEAR(__Start)+15,MONTH(__Start),DAY(__Start))-__Today)*1.),
("20 Years",(DATE(YEAR(__Start)+20,MONTH(__Start),DAY(__Start))-__Today)*1.)
}
VAR __DaysUntil = MINX(FILTER(__Table,[Value2]>0),[Value2])
VAR __Anniversary = MAXX(FILTER(__Table,[Value2]=__DaysUntil),[Value1])
RETURN
__Anniversary)

Best regards

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.