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!
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
)
)