Forum Discussion
Connect CALENDAR table with reporting period table
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 with...
eliasayyy
Memorable Member
Memorable Memberdo you mind sharing a sample data? just copy paste the tbale here if you can
| 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")
)
)