Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated Column Based on Custom Week and Quarter

Hi ,   I want 2 Calculated Column which needs to show like below for Date Table,   Condition - 1.Week Column = Need to Show Present Week and Previous Week , Others need to show as "Others" 2. Q...
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated table.

    Date_Table = CALENDAR(DATE(2020,1,1),DATE(2022,2,1))

    2. Create calculated column.

    Result to be Appear =
SWITCH(
    TRUE(),
    'Date_Table'[Week Num]=WEEKNUM(TODAY())&&YEAR('Date_Table'[Date])=YEAR(TODAY()),"Present Week",
    'Date_Table'[Week Num]=WEEKNUM(TODAY())-1&&YEAR('Date_Table'[Date])=YEAR(TODAY()),"Previous Week",
    'Date_Table'[Week Num]=1&&YEAR('Date_Table'[Date])=YEAR(TODAY())+1,
    "Week"&" "&'Date_Table'[Week Num]&" of "&YEAR(TODAY())+1&" "&" year and "&" "&"Week "&" "&WEEKNUM(DATE(YEAR(TODAY())-1,12,31))&" of "&YEAR(TODAY())&" "&" year",
    "Others")

    3. Result:

    Filter the date to better view the results.

     

    Does this match your expected result.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Anonymous Well, not accounting for the boundary case of year borders, which gets more complex, you could do:

Week Column = 
  VAR __Date = [Date]
  VAR __Today = TODAY()
  VAR __WeekNumDate = WEEKNUM(__Date)
  VAR __WeekNumToday = WEEKNUM(__Today)
RETURN
  SWITCH(TRUE(),
    __WeekNumDate = __WeekNumToday,"Present Week",
    __WeekNumDate = __WeekNumToday - 1,"Previous Week",
    "Others"
  )

 

Hopefully that's enough to get you started. Use QUARTER function instead of WEEKNUM for quarters.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    This is not working, This Showing Present Week and Previous Week for All the Year.(the Weeknum 31 and 32 for all the Year.)

    I want only recent present Week and Previous Week .

    Example -

    1.) Week 32 and Week 31 of Year 2021

     

    Also, if i use in begining of the year (Year-2022-Week-1), it need to show like below

    Week 1 of 2022 year and Week53 of 2021year.

     

    Please help here,

    Greg_Deckler 

    Thanks,

    Mohanraj