MDX to compare two different ranges, but only filtering one range

Question

Hi,

My company wishes a rule to compare measures in two different date ranges, but the user will only put the second range in the where clause, the other range will be calculated by query.
Range2: Give by the user in filter ( We will use Power BI to visual, the slicer component is equal to the where clause in MDX, I think )
Range1: Will be counted the number of days in Range2, and the Range1 will end one day before the beginning of Range2, and will begin depending of the count of days of range2. Ex:

User put '2021/01/11 - 2021/01/15' in the where clause
The range1 will be somehow '2021/01/06 - 2021/01/10'.
The result will be 'MeasureX was 33% greater with Range2 than Range1'.

We already have a MDX to do this comparison, but in our actual MDX we need to put manually the two ranges and without use the slicer/where. Our actual MDX:

member MesRange1 as
aggregate ( Date.Date.[20210106] : Date:Date:[20210110], MeasureX )
member MesRange2 as
aggregate ( Date.Date.[20210111] : Date:Date:[20210115], MeasureX )
member Percent as
(MesRange2 - MesRange1 ) / MesRange1, format_string = 'percent'
select
{ MesRange1, MesRange2, Percent }
from MyCube
where User.MyUser

We need to calculate the first range in a calculated member and put the second range in the where clause( We will use the PowerBI to filter/slice the second range ). How can I do that?

Best Regards,
Luis

Answer 1

Create a dynamic set of [Date].[Date].[Date], let's say its name is [d]. It'll be also helpful in case Power BI decides to pass the filter to the cube as a subselect.
Then [d] is also your Range2, ready to be used in MDX. Range1 is then LastPeriods ( Count ( [d] ) , Head ( [d] ).Item(0).PrevMember )

Answer 2

Hi,

Not exactly sure how Power BI could handle this , but I would tend to try to write parameterized MDX in the measure in Power BI . Example.

Or try to pass parameter to MDX source query from Power BI using M or other Power BI methods. See thread here .

As Power BI is not support in Q&A, you could find more help from Power BI Forum .

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.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 3

Hi @Lukas Yu -MSFT , @Alexei Stoyanovsky ,

Sorry for not answer before, thank you for your help. After many attempts I got it. I will share with you what I did:
MeasureX: This fact table with MeasureX has two surrogate keys, the fisrt is for the day where the register in fact begin, the second is for the day where the register end.
I created in SSAS the members:

member BeginDate as
([Date].[FullDate].CurrentMember).item(0).member_key --( YYYYMMDD format )

member EndDate as
([DateEnd].[FullDate].CurrentMember).item(0).member_key --( YYYYMMDD format )

member RangeInDays as
DATEDIFF
('d'
,CDate(Left(([Date].[FullDate].CurrentMember).item(0).member_key,4)+ "-" + Mid(([Date].[FullDate].CurrentMember).item(0).member_key, 5, 2) + "-" + Right(([Date].[FullDate].CurrentMember).item(0).member_key, 2))
,CDate(Left(([DateEnd].[FullDate].CurrentMember).item(0).member_key,4) + "-" + Mid(([DateEnd].[FullDate].CurrentMember).item(0).member_key, 5, 2) + "-" + Right(([DateEnd].[FullDate].CurrentMember).item(0).member_key, 2))
)

member PastPeriodBegin as
format(DATEADD(
"D",
-RangeInDays,
CDate(Left(BeginDate,4)+ "-" + Mid(BeginDate, 5, 2) + "-" + Right(BeginDate, 2))
), 'yyyyMMdd')

member PastPeriodEnd as
format(DATEADD(
"D",
-1,
CDate(Left(BeginDate,4)+ "-" + Mid(BeginDate, 5, 2) + "-" + Right(BeginDate, 2))
), 'yyyyMMdd')

member MeasureInPast as
AGGREGATE(STRTOMEMBER("[Date].[datekey].[" + PastPeriodBegin + "]"):STRTOMEMBER("[Date].[datekey].[" + PastPeriodEnd + "]") *
STRTOMEMBER("[DateEnd].[datekey].[" + PastPeriodBegin + "]"):STRTOMEMBER("[DateEnd].[datekey].[" + PastPeriodEnd + "]"),
MeasureX)

member MeasureInRange as
AGGREGATE(STRTOMEMBER("[Date].[datekey].[" + BeginDate + "]"):STRTOMEMBER("[Date].[datekey].[" + EndDate + "]") *
STRTOMEMBER("[DateEnd].[datekey].[" + BeginDate + "]"):STRTOMEMBER("[DateEnd].[datekey].[" + EndDate + "]"),
MeasureX)

member Compare as
( (MeasureInRange - MeasureInPast) /MeasureInRange)

Work Fine!!!

Now I have a last problem... to simplify the members above in SSAS I tried to create in SSAS a dynamic named set with:

AGGREGATE(STRTOMEMBER("[Date].[datekey].[" + BeginDate + "]"):STRTOMEMBER("[Date].[datekey].[" + EndDate + "]") *
STRTOMEMBER("[DateEnd].[datekey].[" + BeginDate + "]"):STRTOMEMBER("[DateEnd].[datekey].[" + EndDate + "]") )

And the the MeasureInRange and MeasureInPast will have only: Aggregate( rangeset, MeasureX )

But the side effect of this attempt of this calculated member simplification is that I am receiving a Error in Select "Aggregated Functions cannot be used on Calculated Members".
Without this simplification all the queries work fine and I did Aggregate on Measure. Do you know the reason of the error generated because the simplification attempt? After all, I did Aggregate on Calculated Member and work fine.

Best Regards,
Luis