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

Ok parry2k Keep your MTD,QTD,YTD calculation based on your [Dates].[Calendar] hierarchy using currentMember.Your card show "blank" because your filter context doesn't specify any values on your [Dates].[Calendar] hierarchy.To do that dynamically :1. create a member and a dynamic set to identify your today valueCREATE MEMBER CURRENTCUBE.[Measures].[Now] AS Now(), FORMAT_STRING = "YYYYMMDD", VISIBLE = 0 , DISPLAY_FOLDER = 'Calculation Members'; CREATE DYNAMIC SET CURRENTCUBE.[Today] AS StrToMember("[Date].[Calendar YQMD].[Date].&[" + CSTR(YEAR([Measures].[Now]) * 10000 + Month([Measures].[Now]) * 100 + Day([Measures].[Now])) + "]"), DISPLAY_FOLDER = 'Named Sets';2. Scope your MTD, QTD,YTD and force your [Dates].[Calendar] hierarchy when level ordinal is not as the good depth :SCOPE ([Measures].[Sales Amount YTD]); THIS = CASE WHEN [Date].[Calendar YQMD].level.ordinal = 0 THEN SUM([Today],[Measures].[Sales Amount YTD]) ELSE [Measures].[Sales Amount YTD] END; END SCOPE; SCOPE ([Measures].[Sales Amount MTD]); THIS = CASE WHEN [Date].[Calendar YQMD].level.ordinal < 3 THEN SUM([Today],[Measures].[Sales Amount MTD]) ELSE [Measures].[Sales Amount MTD] END; END SCOPE; You could also alter your cube and set a default value to your date dimension.

I think you can achieve what you are after by never using the MDX NOW() function. Just code your calculated measures using the [Dates].[Calendar].currentMember as you have done and get them right for manually picking the date. Then in your date dimension, add a new column called [Days from today] and make it a DATEDIFF(DAY,datekey,GETDATE()). Then in your Power BI you can select the [Days from today] filter in your report page and set it to = 0 (or 1 for yesterday)

Hello Jolly, I'm glad you proposed this solution which I already tried. When I put the [Date From Today] on the filter, it doesn't automatically shows MTD/QTD/YTD. Just to test, I added date slicer in PowerBI and filter on [Days From Today] and I see date slicer is showing correct date based on the [Days From Today] but MTD/QTD/YTD will only get updated when I actually select date in the slicer, not sure if I'm missing something here. Thanks,P

What kind of visual are you using for your MTD? Are you trying to display a single value? or are you trying to display this as a trend over time?

I'm just showing it as a card. Thanks!

parry2k

Super User

10 years ago

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

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.

Need Help

13 Replies

fbrossard
Kudo Commander
10 years ago
Ok parry2k

Keep your MTD,QTD,YTD calculation based on your [Dates].[Calendar] hierarchy using currentMember.
Your card show "blank" because your filter context doesn't specify any values on your [Dates].[Calendar] hierarchy.
To do that dynamically :
1. create a member and a dynamic set to identify your today value
CREATE MEMBER CURRENTCUBE.[Measures].[Now] AS Now(), FORMAT_STRING = "YYYYMMDD", VISIBLE = 0 , DISPLAY_FOLDER = 'Calculation Members'; CREATE DYNAMIC SET CURRENTCUBE.[Today] AS StrToMember("[Date].[Calendar YQMD].[Date].&[" + CSTR(YEAR([Measures].[Now]) * 10000 + Month([Measures].[Now]) * 100 + Day([Measures].[Now])) + "]"), DISPLAY_FOLDER = 'Named Sets';
2. Scope your MTD, QTD,YTD and force your [Dates].[Calendar] hierarchy when level ordinal is not as the good depth :
SCOPE ([Measures].[Sales Amount YTD]); THIS = CASE WHEN [Date].[Calendar YQMD].level.ordinal = 0 THEN SUM([Today],[Measures].[Sales Amount YTD]) ELSE [Measures].[Sales Amount YTD] END; END SCOPE; SCOPE ([Measures].[Sales Amount MTD]); THIS = CASE WHEN [Date].[Calendar YQMD].level.ordinal < 3 THEN SUM([Today],[Measures].[Sales Amount MTD]) ELSE [Measures].[Sales Amount MTD] END; END SCOPE;

You could also alter your cube and set a default value to your date dimension.
- parry2k
  Super User
  10 years ago
  Thanks for the solution but I see one challenge with this. If user put a date slicer on the report, and select a date, it will always show MTD/YTD for today.
  
  May be I need to tweak the solution to make it work.
  
  Thanks,
  P
  - fbrossard
    Kudo Commander
    10 years ago
    Not at all, it will display the MTD, YTD for the selected date.
    When a date is not selected, it calculate automatically on today else on date/period you select.
- Anonymous
  Not applicable
  9 years ago
  Hello,
  
  I tried your code and I have a problem with the Today Named Set. When I use it in the Sum formula, I get the following error: “OLE DB error: OLE DB or ODBC error: Conversion failed when converting the nvarchar value 'Today' to data type tinyint.; 22018.”
  
  I made a few test and I am not able to use a named set in a Sum.
  
  Did I miss something ?
  
  Thank you
Phil_Seamark
Microsoft Employee
10 years ago
I think you can achieve what you are after by never using the MDX NOW() function.

Just code your calculated measures using the [Dates].[Calendar].currentMember as you have done and get them right for manually picking the date.

Then in your date dimension, add a new column called [Days from today] and make it a DATEDIFF(DAY,datekey,GETDATE()).

Then in your Power BI you can select the [Days from today] filter in your report page and set it to = 0 (or 1 for yesterday)
- parry2k
  Super User
  10 years ago
  Hello Jolly,
  
  I'm glad you proposed this solution which I already tried. When I put the [Date From Today] on the filter, it doesn't automatically shows MTD/QTD/YTD.
  
  Just to test, I added date slicer in PowerBI and filter on [Days From Today] and I see date slicer is showing correct date based on the [Days From Today] but MTD/QTD/YTD will only get updated when I actually select date in the slicer, not sure if I'm missing something here.
  
  Thanks,
  P
  - Phil_Seamark
    Microsoft Employee
    10 years ago
    What kind of visual are you using for your MTD? Are you trying to display a single value? or are you trying to display this as a trend over time?

Forum Discussion

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

13 Replies

Related content

MTD QTD YTD Issue

YTD, QTD, MTD

YTD, QTD, MTD slicers

Slicer MTD, QTD, YTD to filter dates using the slicer.

YTD/QTD/MTD table