Hi!
I'm trying to write month over month and year over year variance formulas using a pre-built calendar table but realized my data (Alerts) are based on 'LastBusinessDayDate' and not a traditional calendar alert. Because of this, my variances and KPIs are all off by a few numbers. 'LastBusinessDayDate' only pulls alerts that were triggered in my database on a business day, not on any given calendar day.
The formulas I built based on traditional calendar logic are:
Month over Month:
=(count({<LM={"1"}>} distinct AlertNum)-count({<MonthsBacks={"2"}>} distinct AlertNum))
/count({<LM={"1"}>} distinct AlertNum)
Year over Year:
=(count({<MonthNum={"<$(=num(month(today())))"},CY={1}>} distinct AlertNum)-count({<MonthNum={"<$(=num(month(today())))"},LY={1}>} distinct AlertNum))
/count({<MonthNum={"<$(=num(month(today())))"},CY={1}>} distinct AlertNum)
I can't figure out how to get the variances to run using the 'LastBusinessDay' logic instead. Any suggestions? Thank you in advance!
