Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Not sure where to enter the Filter syntax.
Thanks in Advance.
Solved! Go to 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.
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.
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.
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
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |