Dynamic (WTD, prior WTD) based on selected Date Filter
Good Morning Enthusiasts!
We have a client requirement to present WTD, MTD, QTD, YTD numbers that would be dynamic depending on the selected/active Date Filter.
Example, if current filter is year 2024, they should see the latest:
WTD (as of Feb 19) total,
MTD (Feb 1-19) total,
QTD (Jan 1 - Feb 19) total,
YTD (Jan 1 - Feb 19) for now.
I'm currently using the Max Date filter measure for these, however, I can't figure out yet how to show the prior WTD, MTD, QTD, YTD numbers.
I attempted to use PASTWEEK(), etc. but there should be an active filter for the week itself before it works.
Am I missing something?
While if I use the This Week, vs Last Week filter, that would only show the current week and prior current week, and is not dynamic if I change the Date Filter.
adding a sample here data here for reference,
if user filters January 2024, they should see the latest WTD for January, then it will be compared to the previous week total.
Any thoughts, suggestions, tips or workaround would be greatly appreciated 🙂