Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
@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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |