MDX for All fiscal periods up to and including last month

Post Reply
M1ndbender
Posts: 24
Joined: Mon Jul 19, 2021 2:15 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: 365

MDX for All fiscal periods up to and including last month

Post by 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
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: MDX for All fiscal periods up to and including last month

Post by 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
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: MDX for All fiscal periods up to and including last month

Post by 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.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX for All fiscal periods up to and including last month

Post by 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
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX for All fiscal periods up to and including last month

Post by 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
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX for All fiscal periods up to and including last month

Post by gtonkin »

MarenC wrote: Wed Jul 20, 2022 9:19 am ...
I think the above should be start position 0 rather than 1, so
...
Maren
Yup, you are correct Maren.
M1ndbender
Posts: 24
Joined: Mon Jul 19, 2021 2:15 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: 365

Re: MDX for All fiscal periods up to and including last month

Post by 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
Posts: 24
Joined: Mon Jul 19, 2021 2:15 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: 365

Re: MDX for All fiscal periods up to and including last month

Post by 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
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: MDX for All fiscal periods up to and including last month

Post by ascheevel »

M1ndbender wrote: Wed Jul 20, 2022 2:55 pm 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
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX for All fiscal periods up to and including last month

Post by 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)
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply