On calculated field, using the date hierarchy causes an error.

[Anonymous]

Hi, everyone. 

I created a date field that pulls the date from one field if populated, otherwise it pulls the date from another field. However, when I try and use the calculated date field in a visualization, I can't use the Date Hierarchy. I can use the continuous dates, however. So for example if I have 100 records that are all in the same quarter, I want to be able to use the hierarchy to show me 4 columns. However, I can only show the date detail level, so there are 100 columns of data (one for each date). 

The two rows I am basing my calculated date on work correctly in the hierarchy. However, when I bring in the calculated field, I get the following error:

Error Message:

The query referenced column 'LocalDateTable_faa99c2d-0e2e-4475-88d0-ab33c8e94d60'[Day] which depends on another column, relationship or measure that is not in a valid state.

Stack Trace:

Invocation Stack Trace:

Activity ID
7ed3c774-d551-1cd4-2af2-b50e29a729c8

Time
Mon May 23 2016 15:37:33 GMT-0400 (Eastern Daylight Time)

Version
2.34.4372.322 (PBIDesktop)

Any ideas what might be going on here? All three columns are formatted as dates and have the same mm/dd/yyyy display formatting.

Thanks,

Scott

[sornavoor]

Wondering if you  tried forceing the calucated column's to "General Date" using the FORMAT dax  expression? 

[sornavoor]

Never mind, Just tried it -  not working with FORMAT either

[Anonymous]

Thanks for trying, @sornavoor! It almost feels like it is getting caught in some type of circular reference, though not sure why, nor if that makes sense. But when the formatting matches and types match, I can't guess what would make it fail!

[sornavoor · Accepted solution]

It is funny - some observations ... was able to show the the hierarchy for calucated date after doig the following 2 actions. 

a) Set the calculated date field to  Date/Time

b) saved the pbix file.

then used the calcualted field in a visual.... 

I'm on Version: 2.34.4372.322 64-bit (April, 2016)

Hierarchy showing up for Date3Logic for the Calculated Date Field

[Anonymous]

Thanks, @sornavoor! Oddly, I started by changing the format, but adding the calculated date to a new visual did nothing. And then when I added in the format function around my if statement, I got an error. It was not until I deleted the format function and forced a recalculation that the visual started showing the proper date hierarchy! Strange, but thanks so much for your help!

Scott

[ericleigh007]

Yes, this is definitely a bug.

If this happens, you have to toggle the formst back and forth.

I toggled back to date, and then back to date/time for my field, and that caused the hierarchy to show up.

Would be great if the team could fix that one.  Took me a whliei to find the answer and then mess around a bit before i was able to work around it.