Forum Discussion
Helper IHow to create a custom week number by date'
Dear super users.
I need to create a column with week number according to a date range since "START" to "END".
TY for your support.
Regards.
Vvelarde disculpa por etiquetarte pero en el pasado tu me ayudaste con tu solución.
I got it !!
#week = var datestart = MAXX(FILTER(TEMPORADAS,TEMPORADAS[INICIO]<='E+R+N'[F. PROD]&&TEMPORADAS[FIN]>'E+R+N'[F. PROD]),TEMPORADAS[INICIO]) return DATEDIFF(datestart,'E+R+N'[F. PROD],WEEK)+1thanks.
10 Replies
MauriceMecoweResolver II
= WEEKNUM([Start],1) 1 in this case is Sunday. If you want Monday use 2 etc.
rmcneish
- Seward12533
Solution Sage
You want to build a date table. With Dates for your entire range and columns that include at least WEEKNumber and Season for every date.
Here is a example of some DAX that will build date table dynamically from the beginning of year 2 years ago to the end of the current year. you can adjsut to iclude a calcuation using SWITCH to calcuate the SEASON. The you you can link your date table to the key date form your FACT table build whatever visuals you want.
DateDIM =ADDCOLUMNS (CALENDAR (DATE(year(today())-2,1,1), DATE(year(TODAY()),12,31)),"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),"Year", YEAR ( [Date] ),"Monthnumber", FORMAT ( [Date], "MM" ),"YearMonthnumber", FORMAT ( [Date], "YYYY-MM" ),"YearMonthShort", FORMAT ( [Date], "YYYY-mmm" ),"MonthNameShort", FORMAT ( [Date], "mmm" ),"MonthNameLong", FORMAT ( [Date], "mmmm" ),"DayOfWeekNumber", WEEKDAY ( [Date] ),"DayOfWeek", FORMAT ( [Date], "dddd" ),"DayOfWeekShort", FORMAT ( [Date], "ddd" ),"Quarter", "Q" & FORMAT ( [Date], "Q" ),"YearQuarter", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ),"TELQuarter", switch(format([Date],"Q"),"1","4","2","1","3","2","4","1"),"TELYear", year([Date])-1962-if(format([Date],"Q")="1",1,0),"TELYearMonthNum",year([Date])-1962-if(format([Date],"Q")="1",1,0)&"-"&format([Date],"MM"),"TELYearMonthShort",year([Date])-1962-if(format([Date],"Q")="1",1,0)&"-"&format([Date],"mmm"),"TELYearQuarter", year([Date])-1962-if(format([Date],"Q")="1",1,0) & "Q" & switch(format([Date],"Q"),"1","4","2","1","3","2","4","1"),"TELYearHalf", year([Date])-1962-if(format([Date],"Q")="1",1,0) & "H" & switch(format([Date],"Q"),"1","2","2","1","3","1","4","2"))- rmcneish
I have a table with the seasons, here is the range dates and i have other table where do i want to put the custom weeks,
Thanks Seward12533
- Seward12533
I'm recommending a single date table that includes the seasons. Its MUCH easier to work with. If you realy want the table of seasons you would add week numbers in your table with the range of dates or just have a measure to calcualte the week number and displaythat. You can use the table as seasons but the measures are messier and if you use the date table with seasons approach PowerBI will do all the work for you and you don't need write measures in many cases and if you do they will be simple ones.
- rmcneish
I got it !!
#week = var datestart = MAXX(FILTER(TEMPORADAS,TEMPORADAS[INICIO]<='E+R+N'[F. PROD]&&TEMPORADAS[FIN]>'E+R+N'[F. PROD]),TEMPORADAS[INICIO]) return DATEDIFF(datestart,'E+R+N'[F. PROD],WEEK)+1thanks.