Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

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

4 REPLIES 4
ClaireJenkins
Frequent Visitor

Hi There,

 

I need a formula to show dates that have either surpassed or blank? Can you help?

v-huizhn-msft
Employee
Employee

Hi @Anonymous,

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

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

Anonymous
Not applicable

It worked perfectly. Thank you very much

 

Martin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.