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

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.

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

    Looks like your using this as a measure the example I geve you was for a calculate column. If so you need to use an aggregate value  you can't just reference and entire column.  Try

    DATEDIFF(MIN('E+R+N'[F. PROD]),RefStartDate,WEEK)

     

    However I still recommend a Calendar Table based approach its much more flexible.

     

    See the attached PBIX file - https://1drv.ms/u/s!AuCIkLeqFmlhhJhuwkc_FWIXnAPPvw

     

    I created a Seasons table

    SeasonStartEnd
    2014 - 14/1/20146/1/2014
    2014 - 26/2/201412/31/2014
    2015 - 11/1/20156/4/2015
    2015 - 26/5/20151/24/2016
    2016 - 11/25/20165/30/2016
    2016 - 25/31/20161/26/2017
    2017 - 11/27/20176/1/2017
    2017 - 211/11/20171/28/2018
    2018 - 11/29/20185/30/2018
    2018 - 27/1/20181/31/2019

     

     

    Then using DAX I created a Calendar Table that includes Seasons and Season Week along with Calendar Week.

     

    DIMDATE = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2014, 1, 1 ), DATE ( 2020, 12, 1 ) ),
    "Calendar Week", FORMAT ( [Date], "WW" ),
    "Season",
        VAR DateCheck = [Date]
        VAR RefStartDate = CALCULATE ( MIN ( SEASONS[Start] ), SEASONS[End] >= DateCheck )
        RETURN
        LOOKUPVALUE ( SEASONS[Season], SEASONS[Start], RefStartDate ),
     "Season Week",
        VAR DateCheck = [Date]
        VAR RefStartDate = CALCULATE ( MIN ( SEASONS[Start] ), SEASONS[End] >= DateCheck )
        RETURN
        VAR Result = DATEDIFF(RefStartDate,[Date],WEEK)+1 RETURN 
        IF(Result>0,Result)
)