Need Help with formula to flag year to date

[tracyhopaulson]

My goal is to create a year to date flag in the calendar table so I can use this field as a filter to display sales for five years.  My current formula flags everyday in June and I need it to stop at today's date, but couldn't figure out how.  Can someone please help untangle this web? I dropped the file in OneDrive folder here.  Thanks in advance for your help.

https://1drv.ms/u/s!AkamwyidmjyMcWJDV5cjYAWKQPc?e=SvO7Lz  

[lbendlin]

You will specify that flag in your Dates table for each of the past years.  For the prior year you can use 

IsPastPY =

VAR LastDatePY = EDATE(MAX('Transactions'[Day]),-12)

RETURN [date]<=LastDatePY

Note that the filter criteria is based on the last day with transactions - which doesn't have to be today - it can be a couple days back. In the interest of fairness (comparing apples to apples) you want to decide which date field to use for the comparison.

[tracyhopaulson]

are you able to access the file I attached in the link? I need ytd for 5 years, not just last year and this year, so how is your formula going to work?

[lbendlin]

for the year before the previous you substitute 12 with 24, then for the year before with 36 , and so on.  They have to be separate columns unless you have regular calendar years.

[tracyhopaulson]

the formula I used pulls sales for multiple years but from Jan 1 to end of June, and I just need it to stop at today June 5th for all years. I would like to have 1 filter for year to date, and having diferent formulas doesn't make sense.

[lbendlin]

In that case modify your column slightly

IsPast =

VAR LastDate = MAX('Transactions'[Day])

VAR DatePattern = MONTH(LastDate)*100+Day(LastDate)

RETURN Month([date])*100+Day([date]) <=DatePattern

The 100 is arbitrary, any number 31 or higher works. Note: This approach only works for calendar years, not for fiscal years that start outside of Jan 1.

[tracyhopaulson]

sorry I don't follow, how does this formula flag the date to mark if it's ytd or not?

[lbendlin]

The formula eliminates the year from the comparison, and only compares the composite Month-Day key against the target Mont-Day limit. It assumes there is no day lower than Jan 1, which is true for calendar years.

You can use a FORMAT function on the dates instead if you don't like the math. 

[tracyhopaulson]

thank you, is it possible to make this formula works with fiscal calendar?

[lbendlin]

My original reply was targeted at fiscal calendars, but you said you didn't want multiple columns.  You could extend that solution by adding those columns up into a combined column.