Leap year in Magic ETL
I'm working on an dataflow (Magic ETL) through which I calculate the MTD, QTD & YTD metrics for both This Year and Last Year data so that I compare the values for each one of these metrics. According to Finance, for leap years, when This Year the last day of Feb is the 28th and Last Year was the 29th, this extra day should be included in the metrics above.
I have created a flow of various steps which end up in 4 'if cases' (filters excluding one another) based on the following logic:
1. This Year (TY) is not 28/Feb or 29/Feb thus Last Year (LY) equals to same date last year, eg. TY=25/3/2019 -> LY=25/3/2018
2. TY = 28th Feb & LY = 28th Feb thus LY equals to same date last year, eg. TY=28/2/2019 -> LY=28/2/2018
3. TY = 28th Feb & LY = 29th Feb thus LY equals to same date last year plus 1, eg. TY=28/2/2021 -> LY=29/2/2020
4. TY = 29th Feb thus LY equals to same date last year minus 1, eg. TY=29/2/2020 -> LY=28/2/2019
The 4 filters along with the preceding steps take a significant amount of time to refresh especially when it comes to large input datasets. I was wondering if anyone else has used a similar logic on leap years in an ETL as I try to find a lighter approach. In my case, this part of the flow is to ensure I don't exclude the 29th of Feb in the MTD, QTD & YTD metrics and compare TY & LY data accurately.