topic Creating a Date table until specific YEARMONTH in DAX Commands and Tips

Creating a Date table until specific YEARMONTH

Dom87326 — Tue, 26 Jul 2022 15:23:28 GMT

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.

Re: Creating a Date table until specific YEARMONTH

amitchandak — Tue, 26 Jul 2022 16:51:52 GMT

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

Re: Creating a Date table until specific YEARMONTH

SpartaBI — Tue, 26 Jul 2022 16:53:01 GMT

Dom87326

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

Showcase Report – Contoso By SpartaBI