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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi  Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

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",
    __WeekNumDate = 1,"Week"&" "&__WeekNumDate&" of "&YEAR(__Date)&" "&" year and "&" "&"Week "&" "&WEEKNUM(DATE(YEAR(__Date)-1,12,31))&" of "&YEAR(__Date)-1&" "&" year",
    "Others"
  )

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

    This is not working, Please test with present and last 2 years of Date.

    It Showing For all the Year Which has ( Week31 and 32).

     

    I need Result like below,

     

     

    Thanks,

    Mohanraj

     

    Anonymous  Greg_Deckler