Forum Discussion
Dom87326
Helper II
Helper IICreating 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
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
Showcase Report – Contoso By SpartaBI
SpartaBI
Community Champion
Community Champion
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
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
Showcase Report – Contoso By SpartaBI