Simple Question - creating future date with color coding - do we need a date table?

[JDBOS]

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!

[zaza · Accepted solution]

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

[zaza · Accepted solution]

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

[JDBOS]

@zaza perfect solution! 😀  

so, for a Measure, why doesn't dax return a single value? what other values is it seeing?

And, is there a "tweak" to show blanks if the Quality Setting Assessment is blank?

Thanks so much for the quick, helpful reply!

[zaza]

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 )

[JDBOS]

@zaza excellent explanation re measures and columns - still a stretch but I'm getting smarter...

And, for your suggestion re the blanks, I tried the following but still missing syntax... but the logic makes sense!

[zaza]

You are missing the VAR declaration in front of __value, sorry I just realized I made an error in my previous response.

Nxt QSA = 
    VAR __value = MAX ('Document Status Tracker'[Last_Qual_Settings_Assess__c] )
    RETURN
    IF ( ISBLANK ( __value ), BLANK(), __value +365 )

[JDBOS]

@zaza - good catch - we need a VAR to get the RETURN to work 😊

But, now the ID's with blank QSA's are no longer displaying in the table...

When I remove the Next column, the records (rows) with blank QSA field are again displayed

So a good solution for not showing ID's with blank fields but since I'm adding about 20 documents (with due dates) to this table

As context, this report is tracking annual documents related to a homeless program - so much paperwork to manage!

Let me know if you have any other coaching - and I can certainly open this as a new Community Question

[zaza · Accepted solution]

To also show the empty values, replace BLANK() with with double quotes ""

measure =
    VAR __value = MAX ( 'Table'[Column] )
    RETURN
    IF ( ISBLANK ( __value ), "", __value + 365 )

[JDBOS]

@zaza - the double quotes worked perfectly - and setting the conditional formatting for the highest range to "white", the blanks don't distract from the report - now I just need to build out the other fields.

Thanks so much for your help!