Forum Discussion

Dom87326's avatar
Dom87326
Helper II
4 years ago
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 ...
calendar
Date
DAX
Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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