[Current
Fiscal Year]
STRTOMEMBER("
[Date].[Fiscal Year].[Fiscal Year].&["
+ Format(Now(),"yyyy")
+"]")
[Previous
Fiscal Year]
STRTOMEMBER("
[Date].[Fiscal Year].[Fiscal Year].&["
+ Format(Now(),"yyyy")
+"]").PREVMEMBER
Previous
Fiscal Year of Months
WITH SET x AS
EXTRACT(STRTOMEMBER("[Date].[Fiscal
Year].&["+cstr(year(now())-1)+"]",CONSTRAINED)*{[Date].[Fiscal
Time].[Fiscal Month]},[Date].[Fiscal Time])
SELECT [Measures].[Actual Billing Amount] ON 0 , x ON 1
FROM [General Ledger];
[Current
Month]
StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ cstr(datepart("q", Now()))
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ Format(Now(),"MM")
+"]"
)
Previous
Month
STRTOMEMBER("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ cstr(datepart("q", Now()))
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ Format(Now(),"MM")
+"].PREVMEMBER"
)
[Last 30
Days]
ORDER(
LASTPERIODS(30, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ cstr(datepart( "q", Now()))
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ Format(Now(), "MM")
+ "].&["
+ Format(Now(), "dd")
+ "]"
))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED),
BDESC)
[Last 3
Fiscal Year]
ORDER(
LASTPERIODS(3, StrToMember("[Date].[Fiscal Time].[Fiscal
Year].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"]"
)),[Date].[Fiscal Time].CURRENTMEMBER.PROPERTIES("ID",
TYPED), DESC)
[Current
Fiscal Months]
ORDER(
StrToMember("[Date].[Fiscal Time].[Fiscal Year].&["
+
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"]&[1].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"]&[1]")
:StrToMember("[Date].[Fiscal Time].[Fiscal Year].&["
+
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"]&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE CSTR(CINT(DATEPART("q", Now())) + 3) END +
"].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"]&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END +
"]"
)
,[Date].[Fiscal Time].CURRENTMEMBER.PROPERTIES("ID",
TYPED), DESC)
[Last 6
Fiscal Months]
ORDER(
LASTPERIODS(6, StrToMember("[Date].[Fiscal Time].[Fiscal
Year].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"]&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE CSTR(CINT(DATEPART("q", Now())) + 3) END +
"].&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN Format(Now(),
"yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END +
"]&[" +
CASE WHEN CINT(Format(Now(),"MM")) >= 4 THEN
CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END +
"]"
)),[Date].[Fiscal Time].CURRENTMEMBER.PROPERTIES("ID",
TYPED), BDESC)
[Last 6
Months]
ORDER(
LASTPERIODS(6, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ cstr(datepart("q", Now()))
+"].&["
+ Format(Now(), "yyyy")
+"]&["
+ Format(Now(),"MM")
+"]"
))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED),
BDESC)
[Last 2
Years]
ORDER(
LASTPERIODS(2, StrToMember("
[Date].[Time].[Year].&["
+ Format(Now(),"yyyy")
+"]"))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED),
BDESC)
[Last
Quarters]
ORDER(
LASTPERIODS(4, StrToMember("[Date].[Time].[Year].&["
+ Format(Now(), "yyyy")
+ "].&["
+ Format(Now(), "yyyy")
+ "]&["
+ cstr(datepart( "q", Now()))
+ "]"))
,[Date].[Time].CURRENTMEMBER.PROPERTIES("ID", TYPED),
BDESC)
