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

 

Example-

DateWeekQuarter
29-07-2021Previous WeekPresent Quarter
30-07-2021Previous WeekPresent Quarter
03-08-2021Present WeekPresent Quarter
04-08-2021Present WeekPresent Quarter
03-05-2021OthersPrevious Quarter
13-06-2021OthersPrevious Quarter
03-03-2021OthersOthers

 

Thanks,

Mohanraj

 

amitchandak Jihwan_Kim Fowmy Greg_Deckler 

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

5 Replies

  • 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

  • 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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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