Date Dimension Table Trouble!

[Gingerjeans88]

Hi all,

Bear with me on this one, a couple of questions: 

I have multiple fact tables, most with multiple date fields. I also have one Option Set table ('Fiscal Year') from Dynamics CRM that has the Fiscal Year date in it as just text (ie. table called 'Fiscal Year' and the values are all just text-type labels '2019', '2020' etc).

I need to be able to have a Fiscal Year slicer across virtually all of my report tabs (and it works so far for the directly related tables), but am struggling with how to get this silly text field into an actual date/year so I can use time intelligence for things like YoY, SamePeriodLastYear etc. 

So I started building a date dimension table using MQuery, added parameters for start of Fiscal Year, custom columns for Fiscal Year, Period, Quarter etc but when I start to relate them to the tables (some of which already have active relationships) all of my previously-created measures turn blank?? Is the filter direction crucial here? 

What am I doing wrong? Also, how would I leverage use of a slicer if the relationship between tables is inactive, since I can't do 'userelationship' when I am just dragging the field into the slicer visual?!?! 

Essentially I only have the Fiscal Year stored in a non-date type field (option set label from CRM) and need to get this somehow into a calendar so I can use time intelligence calculations. Added complication that I need to have the time dimension as well as I am doing some calculations on 'time between stages'. 

@parry2k you are usually my saviour, help please?! Let me know if you need some similar data (this is sensitive) to look at. 

[nvprasad]

Hi,

A couple of points.

1. You need to have an active relationship to filter data using slicer but not for measure calculations. As mentioned in your question you can userelationship to make it active for calculation purposes.

2. Think about creating one more year column in the calendar and change data type to text to establish a relationship with CRM Fiscal year column.

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[Gingerjeans88]

Thanks so much for your help! 

Re point 2. 

So you mean create a new column with the Fiscal Year as Text, then relate the fact table and calendar table using that field? How should I relate it to all of my other fact tables, using the date fields as normal? I still have the issue with my measures showing up blank so hopefully your fix will help that for all?