MDX query to get max date on a range

Question

Hi,
I want to query the tabular model to get 1st date and last date of first month of current quarter and then find the max of snaphot date that lies in that range.Can you pls help with the query
Date column =SnapshotDate
Measure= OrderQty

Answer 1

Hi,
I assume you are talking about current date as for the current quarter.
We could get to 1st month of quart by using following logic:

Quarter Number :Round((MonthNumber + 1)/3)
1st month in Quarter : QuarterNumber*3 - 2

The 1st day in 1st month of current quarter would be :

   With MEMBER [Date].[Date].[firstOfMonthOfQuarter] as OpeningPeriod( [Date].[Calendar].[Date],   
        StrToMember("[Date].[Calendar].[Month].&[" +   
    Format(now(), "yyyy") + "]&[" + CSTR(ROUND ((Month(Now())+ 1)/3)*3-2 ) + "]"))  

And the last date would be :

 MEMBER [Date].[Date].[LastOfMonthOfQuarter]  as ClosingPeriod( [Date].[Calendar].[Date],   
StrToMember("[Date].[Calendar].[Month].&[" +   
Format(now(), "yyyy") + "]&[" + CSTR(ROUND((Month(Now())+ 1)/3)*3-2 ) + "]"))  

Then find the max

  MEMBER [Measures].[x] AS Max(  
[Date].[Date].[firstOfMonthOfQuarter]:[Date].[Date].[LastOfMonthOfQuarter],[Measure].[OrderQty]  
)  
   SELECT [Measures].[x] on 0  
FROM [yourCube]  

You could make adjustment on these code combine them to get what you want.
Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

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.

Answer 2

So if you just want to replace those hardcoded July dates in your query you could do something like the following

DEFINE
    VAR _now = TODAY()
    VAR _year = YEAR( _now )
    // calculate the zero based quarter
    VAR _quarter = QUOTIENT( MONTH( _now ) - 1, 3 )
    VAR _firstMonth = _quarter * 3 + 1
    VAR _startOfFirstMonth = DATE( _year, _firstMonth, 1 )
    VAR _endOfFirstMonth =  EOMONTH( _startOfFirstMonth, 0 )
EVALUATE
SUMMARIZECOLUMNS(
FILTER(
KEEPFILTERS(VALUES('GoalsTarget'[SnapshotDate])),
AND(
'GoalsTarget'[SnapshotDate] >= _startOfFirstMonth,
'GoalsTarget'[snapshotDate] <= _endOfFirstMonth
)
),
"MaxExtractDate", CALCULATE(MAX('GoalsTarget'[SnapshotDate])))

Answer 3

Can't you create a "Quarter" Column in your calendar table and use this as input or filter in your report?
and use MAX(SnapshotDate) to get the max date for that period.