Period over Period Beast Mode

Options
Member

I am trying to create a beast mode that will show me the change from last year's sales to this year's sales. I need it for a table. If anyone has any solutions, that would be amazing.

Tagged:

• Coach
Options

Hello @MarcusJ223

I'd suggest taking a look at this KB Article:

Particularly the Calculating Variance Variance CY-PY and Variance % ((CY - PY)/PY) as these sound like what you're looking to accomplish.

From the article:

Calculating Variance — Variance CY-PY

Use the following code to create a calculation for the variance Current Year - Past Year:

```( SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) THEN `Amount` ELSE 0 END) - SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END) )```

Variance % ((CY - PY)/PY)

Use the following code to create a calculation for the variance % ((Current Year - Past Year) / Past Year):

```( (SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) THEN `Amount` ELSE 0 END) - SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END) ) / NULLIF(SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END), 0) )```

• Coach
Options

Hello @MarcusJ223

I'd suggest taking a look at this KB Article:

Particularly the Calculating Variance Variance CY-PY and Variance % ((CY - PY)/PY) as these sound like what you're looking to accomplish.

From the article:

Calculating Variance — Variance CY-PY

Use the following code to create a calculation for the variance Current Year - Past Year:

```( SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) THEN `Amount` ELSE 0 END) - SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END) )```

Variance % ((CY - PY)/PY)

Use the following code to create a calculation for the variance % ((Current Year - Past Year) / Past Year):

```( (SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) THEN `Amount` ELSE 0 END) - SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END) ) / NULLIF(SUM(CASE WHEN YEAR(`MyDateColumn`) = YEAR(CURRENT_DATE()) - 1 THEN `Amount` ELSE 0 END), 0) )```