Calculated Column Based on Custom Week and Quarter

[Anonymous]

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. Quarter Column = Need to Show Present Quarter and Previous Quarter , Others need to show as "Others"

Example-

Date	Week	Quarter
29-07-2021	Previous Week	Present Quarter
30-07-2021	Previous Week	Present Quarter
03-08-2021	Present Week	Present Quarter
04-08-2021	Present Week	Present Quarter
03-05-2021	Others	Previous Quarter
13-06-2021	Others	Previous Quarter
03-03-2021	Others	Others

Thanks,

Mohanraj

@amitchandak @Jihwan_Kim @Fowmy @Greg_Deckler 

[Anonymous · Accepted solution]

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]

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]

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 

[Greg_Deckler]

@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]

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