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")
)
rmcneish's avatar
rmcneish
Icon for Helper I rankHelper I
8 years ago

 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's avatar
    Seward12533
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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.

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

      TIP check out DATEDIFF function it will return the number of DAY, WEEKS, QUARTERs, YEARS, SECONDS or whatever you want from any two DATE/DATETIME values.

       

      So as a calculated column

      NumWeeks = VAR RefStartDate = CALCULATE(MIN(seasons[StartDate]),seasons[Season]=[Season]) RETURN
DATEDIFF([DateField],RefStartDate,Weeks)

      But if you use the Date Table aproach this is not neccessary.

       

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

        Dear Seward12533, thanks you for helping me, but I have some problems.

         

         

        Well, here is my detailed tables, and I hope you help me.

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

      i would like that!.

       

      I have been working with data since 2001. it's too much.