Calculate Week Days, Days Elapsed and Days Remaining for Current Month

[DickyMohanty]

Hi Team,

Can we built dimensions for ‘Week Days’ (excluding Sat and Sun), ‘Week Days Elapsed’, ‘Week Days Remaining’ for the Current month? 

When the Looker Dashboard will be opened, based on the Month-Year, it will dynamically calculate these 3 KPIs. 

For example today is 12/07/2020:

Week Days: 23 (can be 22 also, if Dec 25,holiday is excluded)

Days Elapsed: 4

Days Remaining: 19

I was able to generate Week Days of a selected Time Range from Filter pane (using start_date and end_date), using this below script. But this will be used as a second option, if the above ones looks difficult (calculating dynamically). 

My Database is BigQuery.

filter: date_filter {
    type: date
  }

  dimension_group: filter_start_date {
    type: time
    timeframes: [raw,date]
    sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '2015-01-01' ELSE CAST({% date_start date_filter %} AS DATE) END;;
  }

  dimension_group: filter_end_date {
    type: time
    timeframes: [raw,date]
    sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CURRENT_DATE ELSE CAST({% date_end date_filter %} AS DATE) END;;
  }

dimension: week_days  {
  type: number
  sql:
     DATE_DIFF(${filter_end_date_raw}, ${filter_start_date_raw}, DAY) + 1 -
     DATE_DIFF(${filter_end_date_raw}, DATE_ADD( ${filter_start_date_raw},INTERVAL 1 DAY), WEEK) -
     DATE_DIFF(${filter_end_date_raw}, ${filter_start_date_raw}, WEEK)  ;;
}