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.

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.

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

         

  • 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.