Forum Discussion

rmcneish's avatar
rmcneish
Icon for Helper I rankHelper I
8 years ago
Solved

How 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...
Need Help
Show and Tell
  • rmcneish's avatar
    rmcneish
    8 years ago

    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)+1

     

    thanks.

Seward12533's avatar
Seward12533
Icon for Solution Sage rankSolution Sage
8 years ago

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")
)