topic Re: Connect CALENDAR table with reporting period table in DAX Commands and Tips

Connect CALENDAR table with reporting period table

jessifi — Mon, 08 May 2023 05:24:58 GMT

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,

Re: Connect CALENDAR table with reporting period table

eliasayyy — Mon, 08 May 2023 06:27:07 GMT

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

Re: Connect CALENDAR table with reporting period table

jessifi — Mon, 08 May 2023 06:37:45 GMT

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

Re: Connect CALENDAR table with reporting period table

eliasayyy — Mon, 08 May 2023 07:34:33 GMT

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

Re: Connect CALENDAR table with reporting period table

jessifi — Mon, 08 May 2023 23:57:19 GMT

ReportPeriodID	Label	From	To
110	2018 Assessment Period 2	3/04/2018 0:00	6/07/2018 0:00
111	2018 Assessment Period 1	29/01/2018 0:00	13/04/2018 0:00
112	2018 Assessment Period 3	23/06/2018 0:00	28/09/2018 0:00
113	2018 Assessment Period 4	15/10/2018 0:00	7/12/2018 0:00
114	2019 Assessment Period 1	29/01/2019 0:00	12/04/2019 0:00
115	2019 Assessment Period 2	29/04/2019 0:00	21/06/2019 0:00
116	2019 Assessment Period 3	22/07/2019 0:00	27/09/2019 0:00
117	2019 Assessment Period 4	14/10/2019 0:00	13/12/2019 0:00
118	2020 Assessment Period 1	28/01/2020 0:00	9/04/2020 0:00
119	2020 Assessment Period 2	27/04/2020 0:00	19/06/2020 0:00
120	2020 Assessment Period 3	22/06/2020 0:00	25/09/2020 0:00
121	2020 Assessment Period 4	12/10/2020 0:00	11/12/2020 0:00
123	2021 Assessment Period 1	27/01/2021 0:00	9/04/2021 0:00
124	2021 Assessment Period 2	27/04/2021 0:00	2/07/2021 0:00
125	2021 Assessment Period 3	19/07/2021 0:00	24/09/2021 0:00
126	2021 Assessment Period 4	11/10/2021 0:00	10/12/2021 0:00
127	2022 Assessment Period 1	31/01/2022 0:00	15/04/2022 0:00
128	2022 Assessment Period 2	2/05/2022 0:00	8/07/2022 0:00
129	2022 Assessment Period 3	25/07/2022 0:00	30/09/2022 0:00
130	2022 Assessment Period 4	17/10/2022 0:00	16/12/2022 0:00
131	2023 Assessment Period 1	30/01/2023 0:00	14/04/2023 0:00
132	2023 Assessment Period 2	1/05/2023 0:00	7/07/2023 0:00
133	2023 Assessment Period 3	24/07/2023 0:00	29/09/2023 0:00
134	2023 Assessment Period 4	16/10/2023 0:00	15/12/2023 0:00

And the calendar is just created from the below DAX.

Dates =

VAR BaseCalendar =

CALENDARAUTO(12)

Return

GENERATE (

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")

)