How to dynamically update MTD/QTD/YTD for SSAS MD

[parry2k]

Hello all,

So far this is very helpful community and able to overcome some of the challenges I have using SSAS MD and PowerBI.

Now here is interesting one and I'm sure it is easy one too. We are planning to have a dashboard that will show MTD/QTD/YTD by connecting to SSAS MD and we want this dashboard to be always based on today's date. I have calculation in the cube but user need to select the date to get MTD/QTD/YTD. What I need to do to automatically update all these charts in dashboard. Here are my calculations in the cube which works when user select the date in the slicer in PowerBI

Calculation based on date selected

MTD -> SUM(MTD([Dates].[Calendar].CurrentMember), [Measures].[Transaction Count])

QTD -> SUM(QTD([Dates].[Calendar].CurrentMember), [Measures].[Transaction Count])

YTD -> SUM(YTD([Dates].[Calendar].CurrentMember), [Measures].[Transaction Count])

Previous Period based on date selected in the slicer

MTD Prev Period -> SUM(MTD(ParallelPeriod([Dates].[Calendar].[Month], 1, [Dates].[Calendar].CurrentMember)), [Measures].[Transaction Count])

QTD Prev Period -> SUM(QTD(ParallelPeriod([Dates].[Calendar].[Quarter], 1, [Dates].[Calendar].CurrentMember)), [Measures].[Transaction Count])

YTD Prev Period -> SUM(YTD(ParallelPeriod([Dates].[Calendar].[Year], 1, [Dates].[Calendar].CurrentMember)), [Measures].[Transaction Count])

Proposed Solution (just giving an example of MTD)

SUM( {StrToMember("[Dates].[Calendar].[Month].&["+vba!Format(NOW(),"yyyy")+"]&[" + vba!Format(NOW(),"MM") + "]", CONSTRAINED)},[Measures].[Transaction Count] )

Here are the challenges I see with this approach:

- if now() is coded in the calculated field in the cube then if user select another date on the slicer, it will always show current MTD and it can be misleading.

- how to achieve previous period based on the above formula?

- I'm sure there is a best way to achieve this, what is that?

Thanks in advance.

P