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 )

 

 

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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors