Hello,
Once again I could use a little help with a specific question. I need to calculate a percentage of recovered patients on one set date one year before the last day of the last completed current month. So as today is March 28th, I will look at the last date of February and than need that date and only that date for last year.
I need this because we want to show percentage of recovered patients for a set moment in time and put the results versus that same set moment in time a year ago, to show whether or not this year we have more recovered patients than we did a year ago at the same set moment. This set moment in time is always the last day of the last completed month, so no flexibility with a date slicer or anything.
This will then show us for example that on 28-02-2022 already 75% of patients for February 2022 and 82% of patients for January 2022 recovered before the end of Feb 2022 (and for Feb 2021 it's 98% of patients at the end of Feb 2022), whereas on 28-02-2021 only 45% of patients for February 2021 and 57% of patients for January recovered before the end of Feb 2021. Does this make any sense?
When I hardcode the date of last year through DATE(2021; 02; 28), the measure (enddate_patient <= DATE(...)) does what I expect it to do and gives me the results for all months prior where the enddate is before the hardcoded date. But when I try to make that DATE flexible depending on the date it uses for the current year, I get errors/failures.
I have tried several things (sameperiodlastyear, Date(Year -1, Month, Day)), but they all give a range of dates for each single month, where I only need the set date one year before the most recent set date, like it is hardcoded.
Is there a way to do this? My feeling says there should be a very simple solution, but for some reason I can't manage to work it out. Hope someone can help me!
