If date is after today

[Anonymous]

I'm trying to create a Measure that returns '1' if the date is after today and '0' if it isn't. The date field is called 'period_end_date' and it uses the default hierarchy of Year, Quarter, Month, and Day. The statement I am trying to use is:

Measure=IF(Table[period_end_date]>TODAY(),1,0)

but I keep getting the error: A single value for column 'proposal_end_date' cannot be determined. This can happen when a measure formula refers to a column that contains many values...

I've also tried: 

Measure=IF(Table[period_end_date].[Date]>TODAY(),1,0)

and I get: A single value for variation [Date] for column 'period_end_date' ... cannot be determined.

Thanks!

[kcantor]

@Anonymous 

You are creating too broad of a measure which lacks the appropriate Row context within your data. @judspud is absolutely correct that a column will work. If the reason you prefer a measure is memory or storage, your field will only have two values, a 1 or a 0 and will compress nicely. There are ways to make a measure work but if you just want to flag dates after today, a column will be the best solution.

[judspud · Accepted solution]

Hi @Anonymous 

What is your reason for using a measure rather than a column?

I have used similar code previously to create a column that identifies if the date is before today.

Try creating a column in the table 🙂

Thanks,

George

[Matthew1900]

In my use case, I am using a measure in order to calculate a cumulative forecasted total based on an average run rate and need to conditionally format the line based on the date - in other words, I would like to be able to make the line invisible (white) before today's date and colored after today's date. Thanks for any guidance!