Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vrocca
Advocate IV
Advocate IV

How to render Year-Month-Day Hierarchy as Date Data Type from SSAS Tabular live data source

Hi,

 

I'm trying to leverage the forecasting feature in Power BI for some of my reports, and one of the requirements is to have a Date value on the X-axis....

 

I am connecting to a SSAS Tabular cube via Live Connection, and have a Date dimension set up. My isssue is that the Year and  Month fields are formatted as Text and don't lend themselves to be plotted on a line chart for forecasting.

 

I'm trying to create derived columns off my Actual Date field, which is of Date data type, and format the display in a way that I can create a hierarchy. 

 

I'm not able to get the date to display as just "yyyy", and I'm not sure what other options to try.

 

Any suggestions?

 

 

 

Thanks,
Victor Rocca | www.victorrocca.com
10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

 

I am not clar about your question.  Are you saying that you have year and month in 2 seperate columns and you want to create a date from those 2 column?  Please show your dataset and the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Date_Cube.png

 

This is how I have the data in the Date table in the SSAS Tabular cube. I want to plot data on a line chart by Year or by Month, and add Forecasting to it. The only way to do that is to have Year and Month columns as a Date data type.

Thanks,
Victor Rocca | www.victorrocca.com

Hi,

 

What problem do you face when you drag the date column to the X-axis?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I am unable to use the Forecast functionality

Thanks,
Victor Rocca | www.victorrocca.com

Hi,

 

Please share the link from where i can download your file and enumerate the steps that you are following to forecast your data.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish - the workbook is a live connection to a SSAS Tabular cube - so I can't give you the exact scenario. Here's a link to a sample PBI report that has the date dimension and the fields described. I am unable to change the data types in PBI because it is a live connection to SSAS Tabular.

 

Notice below, forecast option is not available on the top chart, but does work on the bottom one if using the Actual Date field (Date data type).

 

 

No Forecast Option.png

 

 

Thanks,
Victor Rocca | www.victorrocca.com

So the forecast feature is available only when you have Date on the X-axis (not when you have only years and/or months).  May be that is how it works.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Smiley Happy yes, that is why in my original question I'm asking for a way to format the data type on the back end so that I can have those fields be Date data type and leverage the forecasting functionality. It's a pretty big limitation right now in the tool, but I'm trying to see if someone has a workaround for it. I'm sure I'm not the only one trying to do something like this.

Thanks,
Victor Rocca | www.victorrocca.com

Sorry for not understanding your question earlier.  Perhaps someone else will help.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

No problem! Sometimes it's hard to convey the issue in a few sentences, but thanks for giving it a shot! 🙂

Thanks,
Victor Rocca | www.victorrocca.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.