Forum Discussion

ScottBrown's avatar
ScottBrown
Icon for Helper II rankHelper II
7 years ago
Solved

Fiscal Year Relationshiip

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.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      ScottBrown
      Icon for Helper II rankHelper II
      7 years ago

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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