topic Re: Need Help with formula to flag year to date in DAX Commands and Tips

Need Help with formula to flag year to date

tracyhopaulson — Fri, 05 Jun 2020 17:07:30 GMT

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

Re: Need Help with formula to flag year to date

lbendlin — Fri, 05 Jun 2020 17:52:02 GMT

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.

Re: Need Help with formula to flag year to date

tracyhopaulson — Fri, 05 Jun 2020 18:01:29 GMT

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?

Re: Need Help with formula to flag year to date

lbendlin — Fri, 05 Jun 2020 18:03:51 GMT

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.

Re: Need Help with formula to flag year to date

tracyhopaulson — Fri, 05 Jun 2020 18:32:23 GMT

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.

Re: Need Help with formula to flag year to date

lbendlin — Fri, 05 Jun 2020 18:41:02 GMT

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.

Re: Need Help with formula to flag year to date

tracyhopaulson — Fri, 05 Jun 2020 18:57:56 GMT

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

Re: Need Help with formula to flag year to date

lbendlin — Fri, 05 Jun 2020 19:04:29 GMT

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.

Re: Need Help with formula to flag year to date

tracyhopaulson — Fri, 05 Jun 2020 20:12:43 GMT

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

Re: Need Help with formula to flag year to date

lbendlin — Sat, 06 Jun 2020 11:22:00 GMT

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.