Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dom87326
Helper II
Helper II

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. 

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@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

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

2 REPLIES 2
SpartaBI
Community Champion
Community Champion

@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

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors