Same period last year with partial periods
Updated: Oct 28, 2021
I was helping a friend today with how do we compare partial month this year to just the same partial month last year scenario. When we used SAMEPERIODLASTYEAR by itself, the current month that only had data through to the 24th would be compared to the full month last year.
Here is the solution we came up with:
What it does is to first use LASTNONBLANK() to figure out what the last date is for our chosen measure. That is stored in a variable. Then we use that variable to filter the date table, before giving the date values to SAMEPERIODLASTYEAR(). This is using SAMEPERIODLASTYEAR(FILTER(...)).
See it live here:
Hope this is helpful to you too!
Edit (10/27/2021): added All('Date') to the _lastdatewithdata variable to account for days where there are no sales in the current year but there are values in the previous year.