cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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.

P

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






13 REPLIES 13
Super User
Super User

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

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)


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Super User
Super User

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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Super User
Super User

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

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Super User
Super User

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

Regardless the challenge I have right now to show the MTD but you brought up interesting point about trend for MTD. Since we will be filtering data wheer [Days From Today] = 0, all previous month will not meet filter requirement and will be excluded. Another challenge I believe?

 

Thanks,

P




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Super User
Super User

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

I think I have solved this facing a similar issue.  My solution was to base my calculated measures on the key of the Date Dimension and not any attributes.  This might mean using the Descendants MDX function to drop down to the children to sum up.  I haven't played with this using MTD/QTD etc. but it should work the same.

 

eg, in your caluculated measure, structure your MDX so that it only uses the Key - which is more thank likely to be at the date level.  In your date dimension look for the column with the key icon. 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

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

Unfortunately no success, I used datekey field in the MTD() function but still no success. Not sure what else to try or may be there is something not working at my end.

 

Thanks,

P




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Super User
Super User

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

Any further input or help from someone.


Thanks,

P




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Super User
Super User

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

Anyone else who can help with this? Seems like it is very basic requirement, I cannot move forward with PowerBI dashboard until it is resolved. Hope someone can provide a workaround.

 

Thanks,

P




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin