[SOLVED] trouble with dynamic year filter in MDX

trouble with dynamic year filter in MDX

I have the following query that I use in Azure data factory(this is on the source of a copy action):

 SELECT
    { [Measures].[0INV_QTY],
    [Measures].[0NET_VAL_S] } 
ON COLUMNS,
NON EMPTY
    { [0CUST_SALES].[LEVEL01].MEMBERS *
    [0SALESORG].[LEVEL01].MEMBERS *
    [0COMPANY].[LEVEL01].MEMBERS *
    [0MATERIAL].[LEVEL01].MEMBERS *
    [ZDEBITOR].[LEVEL01].MEMBERS *
    [0FISCPER].[LEVEL01].MEMBERS *
    [0DEB_CRED].[LEVEL01].MEMBERS *
    [0BILLTOPRTY].[LEVEL01].MEMBERS *
    [0DOC_CATEG].[LEVEL01].MEMBERS *
    [0SHIP_TO].[LEVEL01].MEMBERS }
DIMENSION PROPERTIES
    [0SALESORG].[20SALESORG],
    [0COMPANY].[20COMPANY],
    [0CUST_SALES].[80CUST_SALES],
    [0CUST_SALES].[20CUST_GRP1],
    [0CUST_SALES].[20PMNTTRMS],
    [ZDEBITOR].[20CRED_LIMIT],
    [0MATERIAL].[20MATERIAL],
    [0DEB_CRED].[20DEB_CRED],
    [0BILLTOPRTY].[20BILLTOPRTY],
    [0DOC_CATEG].[20DOC_CATEG],
    [0SHIP_TO].[20SHIP_TO],
    [0FISCPER].[80FISCPER]
ON ROWS
FROM $0SD_C03
WHERE ({[0CALYEAR].[2020], [0CALYEAR].[2021], [0CALYEAR].[2018], [0CALYEAR].[2019]})

In here I would like to replace the WHERE with something like Cast(YEAR(GETDATE())-4 as varchar(10)) Now I am really new to MDX and I keep getting stuck. Could anyone point me in the right direction?

So what i want to achieve is not having to adjust the query every year and be able to only have the last 4 years.

Solution

If you are looking for a SQL equivalent as below in MDX

SELECT ... From ... WHERE date > DATEADD(year,-4,GETDATE())

Try using "with member" and function parallelperiod.

CREATE MEMBER CurrentCube.Measures.[Last4Years] AS

ParallelPeriod( [Date].[Date].[Date Yr], 4, StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”))
: StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”)
;