cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinmcmillz Regular Visitor
Regular Visitor

IF Date in field is later than today

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" )
)

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-huizhn-msft
Microsoft

Re: IF Date in field is later than today

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".

1.PNG

Best Regards,
Angelia

View solution in original post

3 REPLIES 3
Microsoft v-huizhn-msft
Microsoft

Re: IF Date in field is later than today

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".

1.PNG

Best Regards,
Angelia

View solution in original post

martinmcmillz Regular Visitor
Regular Visitor

Re: IF Date in field is later than today

It worked perfectly. Thank you very much

 

Martin

Re: IF Date in field is later than today

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,527)