IF Date in field is later than today

[martinmcmillz]

Hi All

I'm reporting on a whole years worth of invoicing in a dashboard and a number of irreservable transaction have been posted into a month that is later in the year from now...So my visual dramically change in a future month because of one stand alone invoice. So I'd essentially like to put in a measure that'll highlight a row as FALSE for being more than today.

Future = IF(now() < DATESMTD('Date'[Date].[Month]),"TRUE","FALSE")

I have a date table being used as such.

Date =
ADDCOLUMNS (
CALENDAR (DATE(2016,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

[v-huizhn-msft · Accepted solution]

Hi @martinmcmillz,

Why you use 'Date'[Date].[Month] in your measure, and compare a month and a date, which is not reasonable.  Please try the following formula, I test it in the sample data you posted and it works fine.

Future = Future =
IF ( NOW () < FIRSTNONBLANK ( 'Date'[Date], 'Date'[Date] ), "TURE", "FALSE" )

Please see the screenshot, because today is 2017/11/17, so all day after today will be taged "TURE".



Best Regards,
Angelia

[benjaminhoang]

How can you change this formula to also count blank collums as true?

[martinmcmillz]

It worked perfectly. Thank you very much

Martin