Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Converting Year and Week Code to actual Date datatype.

Hello, 

 

I'm currently working on a project and I'm trying to create a new calculated column that contains a Bi-Weekly date (i.e. every distinct value in this column is 2-weeks from the other distinct values.) 

I have managed to create a column named 'Bi-WeekYearNumber' which is stored as a number and contains the year & week code in this format: YYYY1WW

 

For example, January 1st, 2021 would be converted to 2021102, January 3rd, 2021 would also be 2021102, and January 15th would be 2021104 and so on.

 

Is there a way to convert the Bi-WeekYearNumber column so that it would output a date? When converting back, the output should go 2021102 -> January 1st, 2021 for all 2021102 values, and January 15th for all 2021104 values and so on. 

 

Please help me and thank you for your time.

 

Thanks!

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , need the following column in date table. or gives an alternate if you want every year's start date needs to be the week start date

     

     

    fortnight
    Start of year = eomonth([Date], -1*month([Date]))+1
    Start of Week Year = [Start of year] - weekday([Start of year]) +1
    Week Start = [Date] - weekday([Date]) +1

     

    fortnight = quotient(Datediff([Week Start], [Start of Week Year], day),7)+1

     

    or

     

    fortnight = quotient(Datediff([Start of year], [date], day),7)+1


    Year fortnight= year([Start of Week Year])*100 + [fortnight])

     

    or

     

    Year fortnight= year([Start of year])*100 + [fortnight])


    fortnight rank = RANKX('Date','Date'[Year fortnight],,ASC,Dense) //YYYYWW format


    These measures can help
    This fortnight = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[fortnight rank]=max('Date'[fortnight rank])))
    Last fortnight = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[fortnight rank]=max('Date'[fortnight rank])-1))