1

Topic: SSAS Calculation Dimension

Greetings,  Calculation Dimension on similarity https://www.sqlbi.com/articles/datetool … mentation/
It works on hierarchy Calendar YQMD: Year/Quarter/Month/Date. All works .
[spoiler as it is made now]

([Dim Date Tool]. [Date Tool Name]. [YTD])
= AGGREGATE
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
PERIODSTODATE
(
[Dim Date]. [Calendar YQMD]. [Year];
[Dim Date]. [Calendar YQMD].CURRENTMEMBER
)
);
FREEZE;
([Dim Date Tool]. [Date Tool Name]. [MTD])
= AGGREGATE
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
PERIODSTODATE
(
[Dim Date]. [Calendar YQMD]. [Month];
[Dim Date]. [Calendar YQMD].CURRENTMEMBER
)
);
FREEZE;
([Dim Date Tool]. [Date Tool Name]. [Last MTD])
= AGGREGATE
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
PERIODSTODATE
(
[Dim Date]. [Calendar YQMD]. [Month];
IIF
(
ISEMPTY (PARALLELPERIOD ([Dim Date]. [Calendar YQMD].Month, 1, [Dim Date]. [Calendar YQMD].CURRENTMEMBER)),
TAIL (DESCENDANTS (ANCESTOR ([Dim Date]. [Calendar YQMD].CURRENTMEMBER, [Dim Date]. [Calendar YQMD].Month).PREVMEMBER, [Dim Date]. [Calendar YQMD]. [Date], SELF), 1).Item (0),
PARALLELPERIOD ([Dim Date]. [Calendar YQMD].Month, 1, [Dim Date]. [Calendar YQMD].CURRENTMEMBER)
)
)
);
FREEZE;
([Dim Date Tool]. [Date Tool Name]. [Last Month])
=
IIF
(
NOT [Dim Date]. [Calendar YQMD].CURRENTMEMBER.LEVEL IS [Dim Date]. [Calendar YQMD]. [Month];
NULL;
AGGREGATE
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
[Dim Date]. [Calendar YQMD].CURRENTMEMBER.LAG (1)
)
);
FREEZE;
([Dim Date Tool]. [Date Tool Name]. [Past Month])
=
IIF
(
NOT [Dim Date]. [Calendar YQMD].CURRENTMEMBER.LEVEL IS [Dim Date]. [Calendar YQMD]. [Month];
NULL;
AGGREGATE
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
[Dim Date]. [Calendar YQMD].CURRENTMEMBER.LAG (2)
)
);
FREEZE;
([Dim Date Tool]. [Date Tool Name]. [Last Week])
=
IIF
(
NOT [Dim Date]. [Calendar WD].CURRENTMEMBER.LEVEL IS [Dim Date]. [Calendar WD]. [Week];
NULL;
AGGREGATE
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
[Dim Date]. [Calendar WD].CURRENTMEMBER.LAG (1)
)
);
FREEZE;
([Dim Date Tool]. [Date Tool Name]. [Past Week])
=
IIF
(
NOT [Dim Date]. [Calendar WD].CURRENTMEMBER.LEVEL IS [Dim Date]. [Calendar WD]. [Week];
NULL;
AGGREGATE
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
[Dim Date]. [Calendar WD].CURRENTMEMBER.LAG (2)
)
);
FREEZE;
([Dim Date Tool]. [Date Tool Name]. [Yesterday])
=
IIF
(
NOT [Dim Date]. [Calendar YQMD].CURRENTMEMBER.LEVEL IS [Dim Date]. [Calendar YQMD]. [Date];
NULL;
AGGREGATE
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
[Dim Date]. [Calendar WD].CURRENTMEMBER.LAG (1)
)
);
FREEZE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [Avg 14 days]);
THIS =
IIF
(
NOT [Dim Date]. [Calendar YQMD].CURRENTMEMBER.LEVEL IS [Dim Date]. [Calendar YQMD]. [Date];
NULL;
AVG
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
LastPeriods (14, [Dim Date]. [Calendar YQMD].CURRENTMEMBER.PREVMEMBER)
)
);
--FORMAT_STRING (THIS) = "#,##0.00;-#,##0.00";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [Avg 4 months]);
THIS =
IIF
(
NOT [Dim Date]. [Calendar YQMD].CURRENTMEMBER.LEVEL IS [Dim Date]. [Calendar YQMD]. [Month];
NULL;
AVG
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
LastPeriods (4, [Dim Date]. [Calendar YQMD].CURRENTMEMBER.PREVMEMBER)
)
);
--FORMAT_STRING (THIS) = "#,##0.00;-#,##0.00";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [Avg 6 weeks]);
THIS =
IIF
(
NOT [Dim Date]. [Calendar WD].CURRENTMEMBER.LEVEL IS [Dim Date]. [Calendar WD]. [Week];
NULL;
AVG
(
{[Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER} *
LastPeriods (6, [Dim Date]. [Calendar WD].CURRENTMEMBER.PREVMEMBER)
)
);
--FORMAT_STRING (THIS) = "#,##0.00;-#,##0.00";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [diff yesterday vs 14 days avg]);
THIS = ([Dim Date Tool]. [Date Tool Name]. [Yesterday]) - ([Dim Date Tool]. [Date Tool Name]. [Avg 14 days]);
--FORMAT_STRING (THIS) = "#,##0;-#,##0";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [% diff yesterday vs 14 days avg]);
THIS = IIF (([Dim Date Tool]. [Date Tool Name]. [Avg 14 days]) = 0, NULL, ([Dim Date Tool]. [Date Tool Name]. [diff yesterday vs 14 days avg]) / ([Dim Date Tool]. [Date Tool Name]. [Avg 14 days]));
FORMAT_STRING (THIS) = "Percent";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [diff last week vs past week]);
THIS = ([Dim Date Tool]. [Date Tool Name]. [Last Week]) - ([Dim Date Tool]. [Date Tool Name]. [Past Week]);
--FORMAT_STRING (THIS) = "#,##0;-#,##0";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [% diff last week vs past week]);
THIS = IIF (([Dim Date Tool]. [Date Tool Name]. [Past Week]) = 0, NULL, ([Dim Date Tool]. [Date Tool Name]. [diff last week vs past week]) / ([Dim Date Tool]. [Date Tool Name]. [Past Week]));
FORMAT_STRING (THIS) = "Percent";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [diff last week vs 6 weeks avg]);
THIS = ([Dim Date Tool]. [Date Tool Name]. [Last Week]) - ([Dim Date Tool]. [Date Tool Name]. [Avg 6 weeks]);
--FORMAT_STRING (THIS) = "#,##0;-#,##0";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [% diff last week vs 6 weeks avg]);
THIS = IIF (([Dim Date Tool]. [Date Tool Name]. [Avg 6 weeks]) = 0, NULL, ([Dim Date Tool]. [Date Tool Name]. [diff last week vs 6 weeks avg]) / ([Dim Date Tool]. [Date Tool Name]. [Avg 6 weeks]));
FORMAT_STRING (THIS) = "Percent";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [diff last month vs past month]);
THIS = ([Dim Date Tool]. [Date Tool Name]. [Last Month]) - ([Dim Date Tool]. [Date Tool Name]. [Past Month]);
--FORMAT_STRING (THIS) = "#,##0;-#,##0";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [% diff last month vs past month]);
THIS = IIF (([Dim Date Tool]. [Date Tool Name]. [Past month]) = 0, NULL, ([Dim Date Tool]. [Date Tool Name]. [diff last month vs past month]) / ([Dim Date Tool]. [Date Tool Name]. [Past month]));
FORMAT_STRING (THIS) = "Percent";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [diff last month vs 4 months avg]);
THIS = ([Dim Date Tool]. [Date Tool Name]. [Last Month]) - ([Dim Date Tool]. [Date Tool Name]. [Avg 4 months]);
--FORMAT_STRING (THIS) = "#,##0;-#,##0";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [% diff last month vs 4 months avg]);
THIS = IIF (([Dim Date Tool]. [Date Tool Name]. [Avg 4 months]) = 0, NULL, ([Dim Date Tool]. [Date Tool Name]. [diff last month vs 4 months avg]) / ([Dim Date Tool]. [Date Tool Name]. [Avg 4 months]));
FORMAT_STRING (THIS) = "Percent";
FREEZE;
END SCOPE;
SCOPE ([Dim Date Tool]. [Date Tool Name]. [% total (under construction)]);
THIS = ([Dim Date Tool]. [Date Tool Name].DEFAULTMEMBER);
FORMAT_STRING (THIS) = "Percent";
FREEZE;
END SCOPE;

[/spoiler]
It is necessary to add alternative names for Month and Date, except how to add additional attributes Month Name Alternate, and Date Name Alternate in  Dim Date to a head more comes nothing. But they will be out of hierarchy Calendar YQMD. As though so  these two attributes on Month and Date in hierarchy?
Russo under the link https://www.sqlbi.com/tools/datetool-dimension/ have any not clear to me dancings if someone explains would be glad.
[spoiler as it at Russo]

//
//CROSS MONTHS
//
SCOPE ([Date]. [Month of Year]. [Month of Year].Members;
[Date]. [Month Name]. [All];
[DateTool]. [Aggregation].AllMembers;
[DateTool]. [Comparison].AllMembers);
///////////////////////////////////////////////////////////////////////////////////////
//It should be scoped do:
//SCOPE ({[DateTool]. [Aggregation]. [Year To Date], [DateTool]. [Aggregation]. [Last 12 Months]});
//but since it is not supported, we use the Except function
SCOPE ([Date]. [Year]. [Year].Members;
Except ([DateTool]. [Aggregation].Members, {[DateTool]. [Aggregation].DefaultMember}));
THIS = Root (StrToMember ("[Date]. [Month Name] .& ["
+ [Date]. [Year].CurrentMember. Properties (' Key ')
+ "] AND ["
+ [Date]. [Month of Year].CurrentMember. Properties (' Key ')
+ "]" ) );
END SCOPE;
///////////////////////////////////////////////////////////////////////////////////////
//The FREEZE is necessary to fix the result against possible modifications made by script
//for lower levels of attribute hierarchy
FREEZE;
END SCOPE;

[/spoiler]

2

Re: SSAS Calculation Dimension

churupaha;
If you then separately do not reassign on attributes of calculation of time that what for to you there freeze?
For the decision of your question it is possible to add through properties anchoring to the necessary attribute, they in hierarchies are perfectly shown (in Excel native representation will be through an additional column, there is a superstructure which substituted attribute - its property, something seems type "OLAP Pivot Extensions", well or directly MDX).

3

Re: SSAS Calculation Dimension

vikkiv;
At us  Power BI - he does not understand sad

4

Re: SSAS Calculation Dimension

churupaha;
Yes, in PowerBI with attribute properties unfortunately while (?) a problem (hangs in sheet of wishes of users), then while if only as a separate measure to do (in most PowerBI it does not turn out yet since connection to SSAS/MD goes through Live Connection and despite likeness it not absolutely Direct Query, so only cubed) if attribute in (separate = to alter/change old reports, to confuse users the navigation, existing = that on navigation) hierarchy not hunting to add.