Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
Solved! Go to Solution.
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
@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
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
16 | |
10 | |
8 | |
8 | |
7 |