MDX Expression as Card Field in SSAS Live Connection based on Slicer criteria

[keobrie]

I have a multi-page PowerBI report with a Live Connection to an SSAS Multidimensional Cube. 

On one of the pages I have a monthly run chart (stacked bar chart) whose scope is governed by a slicer.  In the typical use case, the user selects a time period in the slicer that ends before the last period in the SSAS cube (due in large part to the need to remove data in runout periods).

On the same report tab, I have a Card with a value.  The value is a calculated value stored in the SSAS cube and it is reporting based on the entire period defined by the slicer.

The challenge is that the slicer typically presents 12-24 months but the card needs to report a more current value.  We have determined that we want the card to report the average of the last three months for the slicer period but we want the stacked bar chart to report the entire selected period. 

I have a series of MDX expressions that calculate the proper 3 month value but when the card reports the value, it does not use the last period in the slicer, it appears to use what Excel Pivot tables treat as the "Grand Total" value.

My question is twofold:

"Can I use the last value in the slicer to govern the Card Field value?"

or

"Can I use an MDX expression in the Card Field that in turn uses the last value of the slicer to govern the value in the Card Field value"

Of course, if there is a better way, I'm open to suggestions.

[TeigeGao · Accepted solution]

Hi @keobrie ,

---

"Can I use an MDX expression in the Card Field that in turn uses the last value of the slicer to govern the value in the Card Field value"

---

We cna create two measure for each visual, one uses the full period and another uses the current three months, for the measure of current months, we can refer to the following query:

CREATE MEMBER CURRENTCUBE.[Measures].[current previous 3]  as
SUM
(
   [Date].[Calendar Date].CurrentMember.Lag(3) : 
   [Date].[Calendar Date].CurrentMember.lag(1), 
   [Measures].[Internet Sales Count]
);

If you have other problem related to SSAS, I would suggest you opening a case in SSAS MSDN forum at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices

Best Regards,

Teige