Forum Discussion
Creating a Date table until specific YEARMONTH
Hello,
I'm trying to build a Date table based on my fact Date column, which is int type.
| %DATE_KEY |
| 20220216 |
| 20220210 |
| 20220203 |
| 20220108 |
| 20220116 |
| 20220124 |
| 20220103 |
| 20220111 |
The date table should containt all dates included in the period from DATE (MIN (YEAR) , 1 , 1) and MAX on a Year Month granularity + 1 month. In the above example all dates on a daily granularity from 20220101 until 20220301. I only need the date and not datetime part.
Maybe someone could help?
Thanks.
dimDate = VAR _min_date_key = MIN('Table'[%DATE_KEY]) VAR _max_date_key = MAX('Table'[%DATE_KEY]) VAR _min_year = VALUE(LEFT(_min_date_key, 4)) VAR _max_year = VALUE(LEFT(_max_date_key, 4)) VAR _max_month = VALUE(MID(_max_date_key, 5, 2)) VAR _result = CALENDAR(DATE(_min_year, 1 , 1), DATE(_max_year, _max_month + 1, 1)) RETURN _result
2 Replies
Dom87326 , My Advice would be create full tables with all dates. and join it
example
addcolumns( calendar( date(Quotient(Min(Table[DATE_KEY]), 10000),1,1) , date(Quotient(Max(Table[DATE_KEY]), 10000),1,1) ),
"Date Key" , Year([Date])*10000 + Month([Date])*100 + Day([Date]) ,
"Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte]))
join with date key
dimDate = VAR _min_date_key = MIN('Table'[%DATE_KEY]) VAR _max_date_key = MAX('Table'[%DATE_KEY]) VAR _min_year = VALUE(LEFT(_min_date_key, 4)) VAR _max_year = VALUE(LEFT(_max_date_key, 4)) VAR _max_month = VALUE(MID(_max_date_key, 5, 2)) VAR _result = CALENDAR(DATE(_min_year, 1 , 1), DATE(_max_year, _max_month + 1, 1)) RETURN _result
