relationship between calendar table and fact is not working

[anupampandey]

Hello Team,

I am new to Power BI. I got stuck at one place. I am having 3 fact tables and one Calender table. In fact tables calender month and year is there and in Calender table 2 columns FiscalMonth & FiscalYear are there. I have created a relationship between fact and calender table (on month & year). Now below are the issues:

1. One blank year is appearing in FiscalYear slicer. 
2. I have data only till 201701 but in chart it is taking the data from the previous fiscalyear months (201602 & 201603).
3. If I select any fisical year data in other visuals goes off.

I seriously don't know how to do it know. Is there a issue with relationship or what. Could you help me please? Your valuable help will save me.

Warm Regards

Anupam

[v-caliao-msft · Accepted solution]

Hi @anupampandey,

In your date table, it's calendar year and month, in your fact table, it's fiscal year and fiscal month. You create a relationship between them, there will have Year and month dismatch issue.

In order to work around this issue, you need to have fiscal year and month in your date table. You can use the DAX below to create them.(suppose the fiscal year from July)

FiscalYear = IF('Date'[CalendarMonth]>6,'Date'[CalendarYear]+1,'Date'[CalendarYear])
FiscalMonth = IF('Date'[CalendarMonth]>6,'Date'[CalendarMonth]-6,'Date'[CalendarMonth]+6)

Regards,

Charlie Liao