Connect CALENDAR table with reporting period table

Question

Hi I have set up a simple calendar using the calendar auto function, which looks like this

I am trying to link a reporting period table that has dates as to and From, so that all dates within that period are labelled with the same name. See the table that I am trying to link below.

For example, I would like any date in my caledar between 23/06/2018-28/09/2018 to be labelled 2018 Assessment Period 3. I would be happy to add an extra column to my calendar table called reporting period but everything that I have tried has been unsuccessfull

Thanks,

eliasayyy · Answer

hello after creating a relationship between your calendar table adn fact table (reporting table), you can use lookup or related functions

jessifi · Answer

Hi,I actually already have attempted to use the related function with the below formula but I'm not using this correctly and I'm not sure how to fix it&nbsp;

eliasayyy · Answer

do you mind sharing a sample data? just copy paste the tbale here if you can

jessifi · Answer

ReportPeriodIDLabelFromTo1102018 Assessment Period 23/04/2018 0:006/07/2018 0:001112018 Assessment Period 129/01/2018 0:0013/04/2018 0:001122018 Assessment Period 323/06/2018 0:0028/09/2018 0:001132018 Assessment Period 415/10/2018 0:007/12/2018 0:001142019 Assessment Period 129/01/2019 0:0012/04/2019 0:001152019 Assessment Period 229/04/2019 0:0021/06/2019 0:001162019 Assessment Period 322/07/2019 0:0027/09/2019 0:001172019 Assessment Period 414/10/2019 0:0013/12/2019 0:001182020 Assessment Period 128/01/2020 0:009/04/2020 0:001192020 Assessment Period 227/04/2020 0:0019/06/2020 0:001202020 Assessment Period 322/06/2020 0:0025/09/2020 0:001212020 Assessment Period 412/10/2020 0:0011/12/2020 0:001232021 Assessment Period 127/01/2021 0:009/04/2021 0:001242021 Assessment Period 227/04/2021 0:002/07/2021 0:001252021 Assessment Period 319/07/2021 0:0024/09/2021 0:001262021 Assessment Period 411/10/2021 0:0010/12/2021 0:001272022 Assessment Period 131/01/2022 0:0015/04/2022 0:001282022 Assessment Period 22/05/2022 0:008/07/2022 0:001292022 Assessment Period 325/07/2022 0:0030/09/2022 0:001302022 Assessment Period 417/10/2022 0:0016/12/2022 0:001312023 Assessment Period 130/01/2023 0:0014/04/2023 0:001322023 Assessment Period 21/05/2023 0:007/07/2023 0:001332023 Assessment Period 324/07/2023 0:0029/09/2023 0:001342023 Assessment Period 416/10/2023 0:0015/12/2023 0:00&nbsp;And the calendar is just created from the below DAX.&nbsp;&nbsp;&nbsp;Dates = VAR BaseCalendar = CALENDARAUTO(12) ReturnGENERATE ( BaseCalendar, VAR BaseDate = [Date]VAR YearDate = YEAR (BaseDate) VAR MonthNumber = Month (BaseDate) VAR DayofWeek = DAY (BaseDate) RETURN Row ( "Day", BaseDate, "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT(BaseDate, "mmmm"), "Year Month", FORMAT(BaseDate, "mmm yy"), "Day of Week", FORMAT(DayofWeek,"dddd") ) )

Forum Discussion

Connect CALENDAR table with reporting period table

4 Replies

Related content

Overrule Calendar tabel connection

SSMS connection failure to reporting services

Connect to Shared Calendars

Calendar Slicer with Different Business Calendars

Tweaking Financial Calendar