Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
parry2k
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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

13 REPLIES 13
fbrossard
Advocate V
Advocate V

 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.

 

 

Anonymous
Not applicable

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

PowerBI_Dymanmic_MTD_YTD_SSAS_MD.png

Phil_Seamark
Employee
Employee

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!

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Any further input or help from someone.


Thanks,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.