Forum Discussion
If date is after today
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!
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
3 Replies
- judspud
Solution Supplier
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
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!
- kcantor
Community Champion
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.
