Daily and Monthly Data in Dataset Messing with our YOY Variance Graphic.

ebarb
ebarb ⚪️

We have a dataset that has one monthly roll up value for months from January 2021 till May of 2022. Starting in June of 2022 we started to get daily values from this point on. One of our graphics is a bar and line chart. It has two lines, one for the prior year and one for this year. The bars represent the monthly YOY Variance %. See our beast modes below...

Volume Shipped 2021: SUM((CASE WHEN (YEAR(`SHIPPED_DATE`) = (YEAR(NOW) - 1)) THEN `NEW_PCT_TO_TOTAL` ELSE 0 END))

Volume Shipped 2022: AVG((CASE WHEN (YEAR(`SHIPPED_DATE`) = (YEAR(NOW))) THEN `NEW_PCT_TO_TOTAL` END))

Variance: nullif(((SUM((CASE WHEN (YEAR(`SHIPPED_DATE`) = YEAR(NOW)) THEN `NEW_PCT_TO_TOTAL` ELSE 0 END)) / sum((CASE WHEN (YEAR(`SHIPPED_DATE`) = (YEAR(NOW) - 1)) THEN `NEW_PCT_TO_TOTAL` ELSE 0 END))) - 1), -1)


We were able to adjust our current year beast mode by using the average for the month instead of the sum like the prior year. However, we tried to have a similar approach to our variance beast mode but it continues to display about a 2,000 % increase, which is not accurate as the prior year value for June is 44.86% and this years performance is 46.67%. We believe having the monthly and daily values are messing with our variance bars. We are wondering how we can format this beast mode for the variance so that it accurately displays the variance for YOY monthly variance.