Date Hierarchy Returning (blanks) in Charts

[joshbm1121]

Hey everybody! I'm having an issue with a simple data model I've created and it's an issue I've not come across before with bigger, much more complex models.

I have a sales fact table of about 1000 rows containing sales orders from June to today and there is a column called "Order Date" that I created the relationship on to my date key. My date key is a contiguous range from 6/1/2018-8/31/2018 with calculated columns for the year, quarter, and month. I then created my heirarchy in the canvas area. 

The issue arises when I throw the heirarchy into a line chart to see net sales by month. When I do so, the x-axis of the line chart returns (blank), not the top level of the heirarchy which is Year. When I drill down to the quarter level, I get (blank) (blanks), and when I drill down the month level I get (blank) (blank) (blank).

I double checked and have my Date Key marked as my date table so I'm not sure why this is happening. Like I stated earlier, I've down this same process for much more complex models hundreds of times and have yet to come across this issue. 

Any advice would be much appreciated. 

 

[jpc]

I realise i am responding to a 3 year old post, but i came across this topic when trying to resolve a similar issue, and noticed there was no solution.    In my case, i found that my problem was that my date table didn't cover the full extent of the dates in the data.   I'm no expert, but i imagine that just one date that falls outside the date table range breaks the whole thing, not just the outlying data point(s). 

In my case, i built my date table using CALENDARAUTO() and that fixed the problem.   My previous version had used CALENDAR(startdate, enddate) which had been fine until i added another dataset without realising it contained earlier data, and that's the dataset that refused to display dates properly and showed blank, blank, blank.

[v-shex-msft]

HI @joshbm1121,

Please provide more detail information to help us clarify your scenario, it is hard to troubleshooting from your description and snapshot.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[joshbm1121]

@v-shex-msft

Thanks for getting back. Like I said I have a small fact table of about 1000 rows. The only lookup/dimension table I need for this model is a datekey. I created one in an excel document, will the first date ranging from 1/1/2018 and the last date being 12/31/2018 (transactions included in the fact table were bounded from 6/20-8/20). 

Next, I created several calculated columns on the datekey, i.e. year, quarter, month, month name, etc. Once my datekey and fact table were formatted correctly, I created the relationship between the two. The relationship key was made on the 'DateKey'[Date] field and the 'Planned_Status_Orders'[Order Create Date]. So far so good. Standard precedure, right?

The issue arises when I attempt to pull a date field into a visual. I have a measured created for [Total Sales]. I pull that into a column chart, the put the [Month-Year] field in the axis, and the date range doesn't populate. It returns a (blank) in the axis. No idea why it's doing this.

Like I said in my previous post, I've created hundreds of models, many of which have been more complex, and have never come across this issue. I double checked and made sure my Datekey was Marked as Date Table, and it was. I made sure the field types of the columns on which the relationship was built matched, and they did. 

Any idea?

[v-shex-msft]

HI @joshbm1121,

Power bi not support to create a dynamic calculated column/table based on slicer/filter.

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

Measure is possible to achieve dynamic effect by slicer/filter, but if you drag it to column, it will become fixed values and can't be affected by slicer/filter.

In summary, if your measure formula is based on single selection('selectedvalue', 'hasonevalue' functions) and not works for multiple selection, it will keep 'blank' result in calculated column.

Regards,

Xiaoxin Sheng