Is In YTD Filter

[jfajar]

Hi!

I have a column called "isinytd" which is a "Yes" or "No" Column.

When "Yes" is selected, the current output gives all the months YTD - Jan 2023 To Oct 2023

However, I need a new calculation which gives me all YTD Months up to the previous month. For example, since we are in Oct 2023, When yes is selected, it needs to give me all months YTD - Jan 2023 - Sept 2023

Next month in Nov 2023, the yes filter automatically gives a result of Jan 2023 - Oct 2023

Can I please get help on how to create this filter column? I want to use this filter on visuals so that it automatically considers only the YTD Months I need.

Thank you for your time and help!

[Greg_Deckler]

@jfajar Use a filter of something like:

[isinytd] = "Yes" && [Date] <= EOMONTH(TODAY(), -1)

[jfajar]

@Greg_Deckler Thank you for your update!

I was able to find a reference to this IsInYTD Formula on my backend query. It is as follows:

if Date.IsInYearToDate([Date])=true then "YTD" else "No YTD"

Is there a change I can make to this formula to get the logic I require?

I also tried creating a custom column on the front end based on your suggestion as follows:

IsInYTDTrial = IF([IsInYTD] = "Yes" && 'Calendar'[Date] <= EOMONTH(TODAY(), -1),"YTD", "No")

However, this is the output I get:

[Greg_Deckler · Accepted solution]

@jfajar Is that a calculated column? Are both Date and IsInYTD columns in the same table (Calendar) ? Not sure what's going on with the DAX solution. In Power Query you could do this:

if Date.IsInYearToDate([Column1]) = true and [Column1] <= Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()), -1)) then "YTD" else "No YTD")

[jfajar]

@Greg_Deckler Thank you! This was exactly what I needed!

[jfajar]

HI @Greg_Deckler 

Thank you for your update.

Just as a reference, here is a query on the back end for isinytd:

if Date.IsInYearToDate([Date])=true then "YTD" else "No YTD"

Is there some modification I could make to this back end query for the above logic? 

Here is the logic I used to create a column on the front end based on your suggestion:

IsInYTDTrial = IF([IsInYTD] = "Yes" && 'Calendar'[Date] <= EOMONTH(TODAY(), -1),"YTD", "No")

But the output of this column looks like this: