# YTD Calculation

Member

Hi,

I am trying to calculate a YTD number. I have a card that is set to Current Month and im trying to add a column that includes a YTD number. However, I seem to just keep getting this months numbers in that column. I have tried a variety of beastmodes but none seem to work.

Ive tried

SUM(CASE
WHEN `Fiscal Months` >= '07-2023' AND `Fiscal Months` < '07-2024' THEN `Total Miles` END)

Ive tried partitioning it by year(fiscal Month)

Ive tried adding a column in my dataset called YTD by taking the rows that fall between 07-2023 and 07-2024 and calling it 'Current Year' Then writing a beastmode

SUM(CASE WHEN `YTD` = 'Current Year' THEN `Total Miles` END)

and filtering the card for just current year. Still receive only this months data.

Is the only way to solve this issue to change the date on the card to between 07-2023 and 07-2024 then writing beastmodes that basically sum(Case when fiscal months = month(curdate()) then total miles END

• Coach

Easiest thing to do is have 2 beastmodes: Current month and YTD. When the card is set to current month, only rows that match that criteria will be shown. An alternative would be to use Magic ETL to calculate the YTD value and populate it for every row in the dataset. Then, use that field in the card and just set the field aggregation to MAX.

• Coach

Easiest thing to do is have 2 beastmodes: Current month and YTD. When the card is set to current month, only rows that match that criteria will be shown. An alternative would be to use Magic ETL to calculate the YTD value and populate it for every row in the dataset. Then, use that field in the card and just set the field aggregation to MAX.

• Member

In the ETL version, Would I have to use a group by tile and then join that back to the data set?

• Coach
edited December 2023

For ETL yes, but you need to ensure you group at the level of granularity that your users will be able to play with (so that totals are properly adjusted when filters are applied to the card, otherwise you'll have a fixed value that won't update as filters are applied). The beast mode route, if the card is meant to be responsive to filters, might be a simpler approach (besides avoiding you having to have 2 datasets with basically the same data and just 1 additional column).

As you have stated, the `date range` filter in the card will always be kind of troublesome and most times when I have to show multiple TO DATE values, I leave the date range to be All Time and handle which entries to totalize/use as part of the beast mode calculation with a CASE statement.

You could also use 2 cards with diferent `date ranges` and put them side by side, just as a third alternative.