Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have 4 tables with different dates each (the date format is mm/dd/yy), my guess is that I have to create a "date" dable, I did that with this code:
Hi @Julio9704 ,
Use the calendar function to recreate the date table and create relationships with other tables.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Julio9704 , Few things.
1) You should create a date table using Calendar and calendar auto
2) Month and week depending on format need sort column https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column
3) When Drill from week to a month or vice versa, other will not be complete
Month Year = FORMAT([Date],"mmmm yyyy")
Month Year sort = FORMAT([Date],"yyyymm")
Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense)
Set Sort Column
Year = FORMAT([Date],"YYYY" )
Wee can be sorted on week start date
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
68 |
User | Count |
---|---|
226 | |
129 | |
120 | |
84 | |
78 |