Dynamic date range for past/future date (MDX query in SQL)

Question

Hello,

I need to pull license data from past 90 days to future dates (because it's license data, I have dates ending in future dates for their enrollment end dates) - using MDX query in SQL.
I need dynamic date filter that starts 90 days past from today's date and include all of future dates.

Example) since today is 11/1/2022, dynamic date filter will pull dates from:

• 8/3/2022 (90 days prior to today's date) ~ max end date (I have dates 2025 and beyond)
• [License End Date] is the column I'm using for dates

Current Code I have.. which is very incomplete.
EVALUATE
SUMMARIZECOLUMNS
('license counts'[License End Date], 'Account'[ID],
FILTER('license counts', 'license counts'[License End Date] <= TODAY()-90 || ??

Any help would be appreciated

Answer 1

Hi @light_23 ,
Using MDX to get the last 90 days from today, your statement should look like this:

with set [Last 90 days] as   
{StrToMember("[Date].[Date].[" + Format(now(), "yyyyMMdd") + "]").lag(89): StrToMember("[Date].[Date].[" + Format(now(), "yyyyMMdd") + "]")}  

See below for an example of filtering previous dates: MDX: Filter on Dates Before Today.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.