Forum Discussion
Solved
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 ...
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
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