Good afternoon,
My company reports a monthly census number for each of its Healthcare centers. I compare these numbers to the budget input. Majority of other metrics, like VISITS, NEW PATIENTS or DROPS can be summed for a year to date number, but Census should not be summed as it would report the latest total each month. I am currently using the formula in Beast Mode to calculate the prior month's census without having it summed up:
CASE
WHEN `HCC Census actual` and MONTH(`Date`) = MONTH(ADDDATE(CURDATE(), interval-1 Month)) AND
YEAR(`Date`) = YEAR(ADDDATE(CURDATE(), interval-1 Month)) THEN (`HCC Census actual`)
Else 0
END
The issue with this is usually the prior months financials do not show until 10 days into the next month. For those 10 days prior to the number being reported, there will be no data in the prior month and the census will show as 0.
Need some advice on how to pull the latest census number when not 0 for those days in the month prior to the financials coming in.