Fiscal Year Relationshiip

[ScottBrown]

I have a Fiscal Year Table that shows the Period Start Date and Period End Date with the related Fiscal Year from the ERP.  I am trying to link it to a SharePoint list and based upon the Created Date of the List assign the correct Fiscal Year and Period to it to build the Power-BI artifacts.

I saw the USERELATIONSHIP for the Inactive piece and the relationship for the Active One.

• Created Date >= PeriodStartDate 
• Created Date <= PeriodEndDate

Not sure where to enter the Filter syntax.

Thanks in Advance.

[v-juanli-msft · Accepted solution]

Hi @ScottBrown 

According to your example data

Create measures in "fiscal year table"

start month = MONTH(MAX('fiscal year table'[PeriodStartDate ]))

end month = MONTH(MAX('fiscal year table'[PeriodEndDate]))

Create measures in "sharepoint list"

year = YEAR(MAX('sharepoint list'[created date]))

month = MONTH(MAX('sharepoint list'[created date]))

fiscal year created = 
IF('sharepoint list'[month]>=[start month],'sharepoint list'[year]+1,IF('sharepoint list'[month]<=[end month],'sharepoint list'[year]))

m date = MAX('sharepoint list'[created date])

M-start = CALCULATE(MAX('fiscal year table'[period start date]),FILTER(ALL('fiscal year table'),'fiscal year table'[fiscal year]=[fiscal year created]&&'fiscal year table'[period start date]<=[m date]))

M-end = CALCULATE(MIN('fiscal year table'[period end date]),FILTER(ALL('fiscal year table'),'fiscal year table'[fiscal year]=[fiscal year created]&&'fiscal year table'[period end date]>=[m date]))

M-period = CALCULATE(MAX('fiscal year table'[fiscal period]),FILTER(ALL('fiscal year table'),'fiscal year table'[period start date]=[M-start]&&'fiscal year table'[period end date]=[M-end]))

Please note, in my test, there is no relationship between two tables.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ScottBrown]

Almost; however, the Period Start/End dates are the 12 Fiscal Periods in the Year which they start on different days of the month.  I need to filter the SP List created date between the Period Start & Period End Dates to find the Fiscal Year and Fiscal Period. 

Hope this helps.

[v-juanli-msft]

Hi @ScottBrown 

Look into my previous post i have edited.

In that post, i can create measures in SP List to show correct fiscal year and period for created date according to "fiscal year table".

Best Regards

Maggie