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!

www.perytus.com
(MVP Data Platform)


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