How to do "Today Minus Date Field" in Google Data Studio?

user_13081755 0
Viewed 1258

I have column long_term_remaining_days, and I want to create a Date field which will be calculate TODAY - long_term_remaining_days and in this way will display a Date, for example 1 Jun 2020.

I tried to do as in Excel and used the formula below, but it doesn't work:

TODAY() - long_term_remaining_days

google-data-studio datetime
1 Answers

One way it can be achieved is by using the new PARSE_DATE, UNIX_DATE and CURRENT_DATE functions (introduced in the 17 Sep 2020 update to Dates and Times).

1) Calculated Date

Copy-paste the Calculated Field below which converts CURRENT_DATE to UNIX_DATE (the number of days since epoch, 01 Jan 1970) and subtracts long_term_remaining_days (where long_term_remaining_days represents the respective Number field), after which it's converted to a UNIX_TIMESTAMP by multiplying with 86,400 (seconds in a day) before then being recognised as a Google Data Studio date using the PARSE_DATE function:

PARSE_DATE(
    "%s",
    CAST(((UNIX_DATE(CURRENT_DATE())-long_term_remaining_days)*86400)AS TEXT))

Google Data Studio Report and a GIF to elaborate:

user_128925530
Related