# 30, 60, 90 Day Variance Calculation

Member
edited March 19

UPDATE:

I have this code, but it isn't doint what I need/want

```CASE WHEN MONTH(`Date`) = 1 THEN SUM(CASE WHEN MONTH(`Date`) = 2 THEN `Revenue` ELSE 0 END)END```

What I'm trying to do is say, Ok when the Date is January, grab Feb Revenue and Sum that

I also tried this but it produced nothing:

```(CASE WHEN ((year(Date)=year(curdate())) AND (month(Date)=month(CURDATE()))) THEN CY Revenue END / CASE WHEN (month(curdate())=1) THEN (CASE WHEN ((year(Date)=(year(curdate()) - 1)) AND (month(date)=12)) THEN CY Revenue END ) ELSE (CASE WHEN ((year(date)=year(curdate())) AND (month(date)=(month(CURDATE()) -1))) THEN CY Revenue END ) END) -1```

Tagged:

• Member

Update: Final:

I was able to solve the riddle using the Variables in a beast mode! Oy vey. Now I'm truly dangerous!

```CASE WHEN MONTH('Select Forecast Date - variable') is not null THEN SUM( CASE WHEN 'Accounting Date' = DATE_ADD(Select 'Forecast Date - variable', INTERVAL -3 MONTH) THEN 'Forecast' ELSE 0 END ) end```

• Coach

Something like this?

CASE WHEN MONTH(`Date`) = 1 THEN
SUM(CASE WHEN MONTH(STR_TO_DATE(CONCAT(YEAR(`Date`), '-02-01'), '%Y-%m-%d')) = 2 THEN `Revenue` ELSE 0 END)
ELSE 0
END

Check for the month of "date". If it's January, then SUM the contents…which makes a date string and verifies it's month is 2 (February). If so [revenue] else nothing.

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

• Member

Ok, so I also thought I'd be able to use variables but that is not going to work. I'm just going to build 3 different cards and stick them in the dashboard instead of trying to solve for 30,60,90 in one table.

• Member
edited March 20

@ArborRose The problem is that the data for revenue is on another row, not the same row. So I have to pivot the data some how, or use a variable. But domo doesn't allow variables in that way. I'll have to try to pivot the data somehow

UPDATE: I'm wrong! I understand how to use Variables now. I'm going to update my dashboard and create two date variables and I actually think that will do what I need it to do!

• Coach

I can think of 2 ways to approach this:

ETL and using a window function so you can use LEAD() and have the data pre-aggregated to the Month Level.

Beast Mode since you want to the Sum of February in January you'll need to leverage the FIXED function and write a WHEN statement for each possible month (or at least theoretically). Something like below:

```CASE
WHEN MONTH(`Date`) = 1 THEN AVG(SUM(CASE WHEN DATE_FORMAT(`Date`,'%Y%m') = DATE_FORMAT(`Date`,'%Y02') THEN `Revenue` ELSE 0 END) FIXED())
WHEN MONTH(`Date`) = 2 THEN AVG(SUM(CASE WHEN DATE_FORMAT(`Date`,'%Y%m') = DATE_FORMAT(`Date`,'%Y03') THEN `Revenue` ELSE 0 END) FIXED())
…
WHEN MONTH(`Date`) = 12 THEN AVG(SUM(CASE WHEN DATE_FORMAT(`Date`,'%Y%m') = DATE_FORMAT(DATE_ADD(`Date`,INTERVAL 1 MONTH),'%Y01') THEN `Revenue` ELSE 0 END) FIXED())
END
```

Be aware that for Month 12, you need to get the number for January next year, so you need the year number displaced by 1.

• Member
```CASE WHEN MONTH('Select Forecast Date - variable') is not null THEN SUM( CASE WHEN 'Accounting Date' = DATE_ADD(Select 'Forecast Date - variable', INTERVAL -3 MONTH) THEN 'Forecast' ELSE 0 END ) end```