Forum Discussion
Simple Question - creating future date with color coding - do we need a date table?
This feels like a simple question but I'm having trouble making it work...
Our goal is to create a new date column "Next Qualified Setting Assessment" with a date that is one year from the "Last Qualified Setting Assessment", and then create a "Days remaining" measure (or column?) that we can use to conditionally format the Next Date so they are easy to identify.
Seems like DateAdd function would work but that seems to rely on a Date table.
I've seen recommendations to just "add 365" but the following test doesn't work
Also saw that "Date.adddays" could be an option but syntax is wrong in the following
Any suggestions would be much appreciated - and if building a Date Table is the best option, I'm certainly open to that but would like to understand why...
Thanks in advance!
Your test doesn't work because a measure needs to be able to return a single value.
try:
nxt QSA = MAX ( 'Document Status Tracker'[Last_Qual_Setting_Assess__c] ) + 1
If you make a calculated column instead of a measure, than the syntax you were trying would work:
nxt QSA = 'Document Status Tracker'[Last_Qual_Setting_Assess__c] + 1
To also show the empty values, replace BLANK() with with double quotes ""
measure = VAR __value = MAX ( 'Table'[Column] ) RETURN IF ( ISBLANK ( __value ), "", __value + 365 )
8 Replies
Your test doesn't work because a measure needs to be able to return a single value.
try:
nxt QSA = MAX ( 'Document Status Tracker'[Last_Qual_Setting_Assess__c] ) + 1
If you make a calculated column instead of a measure, than the syntax you were trying would work:
nxt QSA = 'Document Status Tracker'[Last_Qual_Setting_Assess__c] + 1
A measure is a calculation, and as any calculation it returns a singular value. If you specify a column in your measure, you need to use an aggregation such as MAX/MIN/SUM. Otherwise what you're saying is add 1 and "Column" together. But what is Column? Column is not a number, so the calculation cannot return a result. But, MAX of Column IS a number, so that is why that works.
to show blank values try:
measure = VAR __value = MAX ( 'Table'[Column] ) RETURN IF ( ISBLANK ( __value ), BLANK(), __value + 365 )
