My Main issue is how can I link to the Dates table
Page 1 has the correct output but Page 2 is incorrect. I need to link to Dates table so that I can build a Time intelligence reporting.
You can take a glance on the other Tabs to see how the data flow with out the Date table.
For this issue just focus on Page 1 without Date table and Page 2 with Date Table
All of the Education data will use School_LKP table. All other tables will be similar to Assessments table with School_Key add to table
This an Education sample data.
Tables
School_LKP is the main table with School_Key is the primary key field.
AssessmentsMod is a duplicate of Assessments Table but I added to columns School_Year and School_date. I added school_date so that I can link to the Dates table.
School_Date =
SWITCH(
TRUE(),
Dates[Date] <= DATE ( 2015, 8, 31 ), “9/1/2014”,
Filter By all schools.pbix (198.5 KB)
Dates[Date] <= DATE ( 2016, 8, 31 ), "9/1/2015",
Dates[Date] <= DATE ( 2017, 8, 31 ), "9/1/2016",
Dates[Date] <= DATE ( 2018, 8, 31 ), "9/1/2017",
Dates[Date] <= DATE ( 2019, 8, 31 ), "9/1/2018",
Dates[Date] <= DATE ( 2020, 8, 31 ), "9/1/2019",
Dates[Date] <= DATE ( 2021, 8, 31 ), "9/1/2020",
Dates[Date] <= DATE ( 2022, 8, 31 ), "9/1/2021"
)
SchoolYear =
SWITCH(
TRUE(),
Dates[Date] <= DATE ( 2015, 8, 31 ), “2014”,
Dates[Date] <= DATE ( 2016, 8, 31 ), “2015”,
Dates[Date] <= DATE ( 2017, 8, 31 ), “2016”,
Dates[Date] <= DATE ( 2018, 8, 31 ), “2017”,
Dates[Date] <= DATE ( 2019, 8, 31 ), “2018”,
Dates[Date] <= DATE ( 2020, 8, 31 ), “2019”,
Dates[Date] <= DATE ( 2021, 8, 31 ), “2020”,
Dates[Date] <= DATE ( 2022, 8, 31 ), “2021”
)