PowerBI Dax Measure Sales Prior Year apply Max(Date)

Question

I have a "Total Sales" measure and a "Sales Prior Year" Measure

Sales Prior Year = CALCULATE([Total Sales], DATEADD('calendar'[full_date], -1, YEAR))

However, I designed the calendar filter to be like the following: Users can select the year, quarter, and month, and specific date ranges independently.

However, my default page is a bookmark with: "Year:2024", "Quarter:ALL", "Month:ALL" selected, and clear selection is applied in the date range selector. (Previously I have applied MAX(date) to be my most recent invoice date in Power Query Editor, that is why it shows as 4/1/2018 to 4/16/2024, rather than my entire date range in my calendar table)

The "clear selection" act as no date is selected, which is what i want because it will update the date itself to the most recent invoice date everyday I run a new update. However, Because of this behavior, When comparing prior year sales, PowerBI thinks I want to compare the entire months rather than entire months + partial month.

For example, my most recent invoice date is "2024-04-16", in my Month filter, it will check all 4 months (1,2,3,4) which means the dax measure will calculate numbers for "2023-01-01" to "2023-04-30" instead of "2023-04-16".

My question is, is there a way to also add a CAP to my measure so that in cases like this, where no specific date is selected, it will still apply comparison for the same period?

Thank you!

Answer 1

This should work I think:

Sales Prior Year = 
    VAR LatestDateCurrentYear = MAX('YourSalesTable'[Invoice date])
    VAR LatestDateLastYear = DATE(YEAR(LatestDateCurrentYear) - 1, MONTH(LatestDateCurrentYear), DAY(LatestDateCurrentYear))
    VAR FirstDateLastYear = DATE(YEAR(LatestDateCurrentYear) - 1, 1, 1)
    RETURN
    CALCULATE(
        [Total Sales],
        FILTER(
            ALL('calendar'[full_date]),
            'calendar'[full_date] >= FirstDateLastYear && 'calendar'[full_date] <= LatestDateLastYear
        )
    )