MDX for All fiscal periods up to and including last month

[M1ndbender]

Hello everyone,

We have a zero level fiscal period dimension that shows dates in the following format JUL-22. These dates go all the way out to 2027.

I am trying to write an MDX subset that will return all data for all periods prior to current month.

If I ran it is July-22 I would like all fiscal period until the end of Jun-22, if I ran it in Aug-22 I would like all fiscal periods included up to then end of JUL-22.

I am assuming this is possible but I am at loss as to where to even start. I am really new to MDX.
Thanks in advance

[ascheevel]

One way that keeps the MDX simple is to leverage an attribute on your fiscal period dim to specify when a given month is in the past. You'd then apply a Filter statement in the MDX to select only completed months. Assuming your dim is called "fiscal period" and you have an attribute "month_complete" that will be populated with a "y" when the month is complete, the mdx could look like the below. You'd of course need to maintain the attribute.

Code: Select all

Filter(
    TM1FilterByLevel(
        [fiscal period].members]
    , 
        0)
, 
    [fiscal period].[month_complete] = "y"
)

[ascheevel]

I'll add that you can also use the Range operator if the element indexes are ordered based on calendar date. In it's simplest form, it could look like this

Code: Select all

{[fiscal period].[Jan-20] : [fiscal period].[Jun-22]}

Your requirement that the the latest period in the subset should be the prior month of current would add some complexity to the MDX where you'd possibly have to use StrToMember and reference a prior month attribute on a current month element.

[gtonkin]

Something like this may work:

Code: Select all

{SUBSET(
TM1FILTERBYLEVEL( {TM1SUBSETALL( [Fiscal Period] )}, 0),1,
VAL([Fiscal Period].[JUL-2022].Properties("Member_Ordinal"))-1
)}

Obviously JUL-2022 is hard-coded but you could look at converting to a lookup in another set or cube.

Essentially we are look at a list of elements and taking from element 1 to N-1 where N is the index of the current month.
There are no doubt many other ways to do this, like the range method and there are definitely considerations where there are C levels too.

Have a look in the Tips and Tricks for more.

[MarenC]

Hi,

{SUBSET(
TM1FILTERBYLEVEL( {TM1SUBSETALL( [Fiscal Period] )}, 0),1,
VAL([Fiscal Period].[JUL-2022].Properties("Member_Ordinal"))-1
)}

I think the above should be start position 0 rather than 1, so

Code: Select all

{SUBSET(
TM1FILTERBYLEVEL( {TM1SUBSETALL( [Fiscal Period] )}, 0),0,
VAL([Fiscal Period].[JUL-2022].Properties("Member_Ordinal"))-1
)}

Maren

[gtonkin]

...
I think the above should be start position 0 rather than 1, so
...
Maren

Yup, you are correct Maren.

[M1ndbender]

Thank you for the help. When I try that updated with the actual dimension name and date format, I get everything from Dec-11 to Mar-27 returned by the mdx.

Did I mess it up somehow?

Code: Select all

{{SUBSET(TM1FILTERBYLEVEL( {TM1SUBSETALL( [fixed_asset_fiscalPeriod] )}, 0),0,VAL([fixed_asset_fiscalPeriod].[JUL-22].Properties("Member_Ordinal"))-1)}}

[M1ndbender]

I was also trying something like this but can't get the second half to default to previous month...always says invalid mdx statement

Code: Select all

[fixed_asset_fiscalPeriod].[fixed_asset_fiscalPeriod].[2011.12] : FILTER(TM1SUBSETALL([fixed_asset_fiscalPeriod]) , [fixed_asset_fiscalPeriod].[NextPeriod] = [CONTROL].([Control Parameters].[Current Month],[CONTROL_m].[S-Value]))

[ascheevel]

I was also trying something like this but can't get the second half to default to previous month...always says invalid mdx statement

Code: Select all

[fixed_asset_fiscalPeriod].[fixed_asset_fiscalPeriod].[2011.12] : FILTER(TM1SUBSETALL([fixed_asset_fiscalPeriod]) , [fixed_asset_fiscalPeriod].[NextPeriod] = [CONTROL].([Control Parameters].[Current Month],[CONTROL_m].[S-Value]))

You're missing curly brackets and I'm not sure if you can have a Filter statement nested in the range operator like that. Also, if you have a control cube specifying the "Current Month", do your MDX a favor and specify a "Prior Month" element in the Control Parameters dim and populate in your Control cube. If you do that, the below MDX should work (barring any syntax errors/typos on my part).

Code: Select all

{
[fixed_asset_fiscalPeriod].[fixed_asset_fiscalPeriod].[2011.12]
:
StrToMember("[fixed_asset_fiscalPeriod].[fixed_asset_fiscalPeriod].[" + [CONTROL].([Control Parameters].[Prior Month],[CONTROL_m].[S-Value]) + "]")
}

[Wim Gielis]

Or, let's introduce LAG to go back 1 period, no need for a different parameter in the CONTROL cube:

Code: Select all

[fixed_asset_fiscalPeriod].[fixed_asset_fiscalPeriod].[2011.12]
:
StrToMember("[fixed_asset_fiscalPeriod].[fixed_asset_fiscalPeriod].[" + [CONTROL].([Control Parameters].[Current Month],[CONTROL_m].[S-Value]) + "]").Lag(1)