I created a Column in my Dates table that looks at the Date and determines if it is eligible for a multi-year filter (“Include Multi-Year Future”) that follows this logic:
- Looks at the Year in the [Date] Column and subtracts 1 to include the prior year.
- Looks at that result and determines if it is in the next 4 years.
- Returns a ‘Y’ if the “Multi-Year Future” flag has been triggered.
For an unknown reason, the return is coming up as an “N” for No - the date does not fall within the Multi-Year range, even for values in 2022 and 2023. I can’t figure out what I’ve done wrong in my syntax:
= Table.AddColumn(#“Changed Type with Locale2”, “Include Multi-Year Future”, each if Date.IsInNextNYears(Date.AddYears([Date],-1),4) then “Y” else “N”),
The business case for this is that each year our Sales team may sell multi-year contracts for the following 3 years. They want the period of comparison to be from the prior year out for the next 3 years (so a 4-year period, beginning prior year). I want to build a model that projects out all the sales for those contracts, including for future years and set up a filter to include the Sale if it falls in the “Multi-Year” flag range (i.e, the Flag has a value of “Y”).
I have included my Data model for the Dates query based on the Deals table pulled in from an external database.
Appreciate your expertise. . .
Kathryn S.
(
Multi-Year Flag.pbix (647.8 KB)
)
Multi-Year Flag_DESIRED RESULTS.xlsx (117.9 KB)
This function will indicate whether the given datetime value occurs during the next number of years, as determined by the current date and time on the system. That’s why its not returning the expected result.
You could try a combination like this:
Table.AddColumn(#"Changed Type with Locale2", "Include Multi-Year Future", each if (Date.IsInPreviousNYears([Date], 1) or Date.IsInCurrentYear([Date]) or Date.IsInNextNYears([Date], 2)) then "Y" else "N" )
Or create a custom date list and see if the date is present…
I hope this is helpful
1 Like
Thank you @Melissa.
I thought that by providing a -1 parameter on the Date.AddYears function that it would take into account current year less 1, so: 2021. Looking at the expressions for the other date flags in the Advanced Editor, I see Date.AddYears with a -1 parameter:
#“Two Year Flag” = Table.AddColumn(#“Changed Type with Locale1”, “Year In Last Two Years”, each if Date.IsInPreviousNYears(Date.AddYears([Date],-1),2) then “Y” else “N”)
The return of this expression is years 2021 and 2022. Why is the Date.AddYears with -1 returning 2021 in that line, but not in mine?
So all *IsInPrevious, *IsInCurrent or *IsInNext compare against the current date and time on the system.
I’ve separated the components to illustrate what is happening.
You are looking for dates in the last 2 years (as the current year is 2022) the past 2 years are 2020 and 2021. Offsetting the input value will shift results by a year returning TRUE for 2021 and 2022 in the Date column.
I hope this is helpful
2 Likes
Hi @kjssdca, did the response provided by @Melissa help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
Hi @kjssdca, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
