# Compare against a specific date?

Options
Contributor

I have a dataset that aggregates data on a daily basis. I would like to compare the most recent value to the value on a specific date.

Example:

Date Value

1/1/24 400

1/2/24 403

1/3/24 410

….

5/7/24 500

In my Multi Value card, I am using MAX of Date and Value to pull the value 500, then for comparison, I have the all the date options (days, Quarters, Years, etc…)

I want to be able to get a % change between 5/7/24 and 1/1/24 (IE % change year to date).

Any ideas?

Tagged:

• Coach
Options

@mberkeley you can use the "Multi-value columns" comparative card.

Then use the first part of the previous beast mode to calculate the current value

```AVG(case when most_recent_flag = 1 then value end)
```

And the second part to calculate the comparison value

```AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)
```

Then set it up like this.

David Cunningham

** Did this solve your problem? Accept it as a solution! ✔️**

• Coach
Options

@mberkeley my suggested approach would be…

Step 1: In your ETL, use the rank and window tile to assign the most recent data a value of 1. This will be used to filter in a beast mode. Say we call this MOST_RECENT_FLAG

Step 2: Create your beast mode. You can set your comparison date by adjusting the values in the STR_TO_DATE function.

```(AVG(case when most_recent_flag = 1 then value end)
AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end))
/
AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)
```

You can see that this allows us to calculate a YTD change with the most recent value. In this example dataset I used, the last date with values is March 31st.

Happy to answer any questions you may have 😁

David Cunningham

** Did this solve your problem? Accept it as a solution! ✔️**

• Coach
Options

I have given this example in previous answers. Say you create a formula for the current year. You can use the same formula to also find the value for the year before {previous year}. To find the difference, just subtract one from the other.

This should be the same thing you are working with. The difference being, you want to compare to a static date.

```Current Year (YTD):

sum(
case when YEAR(mydate) = YEAR(CURRENT_DATE())
and mydate <= CURRENT_DATE()
then amount else 0 end
)

Previous Year (YTD):
sum(
then amount else 0 end
)

Difference between CY and PY:
sum(
case when YEAR(mydate) = YEAR(CURRENT_DATE())
and mydate <= CURRENT_DATE()
then amount else 0 end
)-
sum(
then amount else 0 end
)```

** Did this solve your problem? Accept it as a solution! **

• Contributor
Options

@david_cunningham Thanks. I am able to get the values for the dates and do the math as you suggested. What I can't figure out is how to use the multivalue card to show the Current Value and then the % change. like this:

• Coach
Options

@mberkeley you can use the "Multi-value columns" comparative card.

Then use the first part of the previous beast mode to calculate the current value

```AVG(case when most_recent_flag = 1 then value end)
```

And the second part to calculate the comparison value

```AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)
```

Then set it up like this.

David Cunningham