cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fbrossard Member
Member

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

 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.

 

 

Super User
Super User

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

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






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

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





fbrossard Member
Member

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

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

Highlighted
Ilocans Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 98 members 1,532 guests
Please welcome our newest community members: