cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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.

 

Dax Dates 1.0a 2020-04-26 qual setting assess as date.jpg

 

I've seen recommendations to just "add 365" but the following test doesn't work

Dax Dates 3.0a 2020-04-26 error msg1.JPG

 

Also saw that "Date.adddays" could be an option but syntax is wrong in the following

Dax Dates 4.0a 2020-04-26 error msg2.jpg

 

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!

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver II
Resolver II

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

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

 

View solution in original post

Highlighted
Resolver II
Resolver II

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

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

 

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

 

View solution in original post

8 REPLIES 8
Highlighted
Resolver II
Resolver II

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

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

 

View solution in original post

Highlighted
Helper II
Helper II

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

@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!

 

Dax Dates 5.0a 2020-04-26 next qsa max.jpg

Highlighted
Resolver II
Resolver II

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

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 )

 

 

Highlighted
Helper II
Helper II

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

@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!

 

Dax Dates 6.0 2020-04-26 next qsa max.JPG

Highlighted
Resolver II
Resolver II

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

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 )

 

 

 

 

Highlighted
Helper II
Helper II

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

@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

Highlighted
Resolver II
Resolver II

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

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

 

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

 

View solution in original post

Highlighted
Helper II
Helper II

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

@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!

 

Dax Dates 7.0 2020-04-26 next qsa max.JPG

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors